convert table layout - 3 simple fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

convert table layout - 3 simple fields

Author

Topic

xianwinwin
Starting Member

17 Posts

Posted - 2006-06-07 : 15:45:41

can anyone assist me with this one:
I am trying to convert this table:

thanks

+------+-----------+-------------+
| ID | FILE_TYPE | amount |
+------+-----------+-------------+
| 1 | U | 200 |
| 2 | U | 249 |
| 3 | U | 114 |
| 4 | U | 249 |
| 5 | U | 176 |
| 1 | F | 1722 |
| 2 | F | 1890 |
| 3 | F | 2088 |
| 4 | F | 1169 |
| 5 | F | 1768 |
+------+-----------+-------------+

to this:

+------+----------+---------+
| ID | AMOUNT_U | AMOUNT_F|
+------+----------+---------+
| 1 | 200 | 1722 |
| 2 | 249 | 1890 |
| 3 | 114 | 2088 |
| 4 | 249 | 1169 |
| 5 | 176 | 1768 |
+------+----------+---------+

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 18:11:14

[code]select ID,
sum(case when FILE_TYPE = 'U' then amount end) as AMOUNT_U,
sum(case when FILE_TYPE = 'F' then amount end) as AMOUNT_F
from table
group by ID[/code]