How do I combine multiple rows into a single row with multiple columns

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)

How do I combine multiple rows into a single row with multiple columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-24 : 18:09:38

Jonathan writes "I have a CUBE function which returns both the net and gross areas of a building (based on a room data table). The results are returned with a flag 1=net, 2=gross in mutiple rows as below


building    flag	area
1	    1	        103
1	    2	        121
10	    1           2349
10	    2	        3199
11	    1   	2047
11	    2	        2972

I want to return the results as a single row per building as below


building net   gross
1        103   121
10       2349  2349
11       2047  2972

How can I achieve this?"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-24 : 19:16:54

Woops - sorry I didn't read the question properly....

I thought you wanted them concatenated into one column!

Cheers nr

Edited by - rrb on 03/24/2002 19:33:27

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-24 : 19:28:16

select
building ,
net = sum(case when flag = 1 then area else 0 end) ,
gross sum(case when flag = 2 then area else 0 end)
from #a
group by building
order by building

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources