Hi All,
I need to write a SQL for reporting. Here are the schema of the one of tables:

ID Amt_A Amt_C Amt_C
1 100.00 0.00 200.00
2 0.00 120.00 0.00
3 0.00 133.00 300.00


I need to write a sql to display those amount in separate row (amt > 0) as below:

ID AMT
1 100.00
1 200.00
2 120.00
3 133.00
3 300.00

I could only think of one way in doing it, I would alias the table into 3 tables and each of them would select the "ID" and one of the "Amt_x" column, then join them again. But its seems not efficient enough. As the above table is only one of the tables in the query, and the data size in Production environment is large, I am afraid the performance would be very slow.

Is there any other smarter or simpler way to do that?

Thank you very much.

Use the UNION ALL statement. You will have 3 SQL statements in the UNION ALL, each with 2 columns in the select list. Check the SQL Reference for usage if you don't understand.

victorlung,
try the following:
Hope this helps,
Grofaty