Convert multiple rows into 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)

Convert multiple rows into multiple columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-31 : 08:46:41

Robert writes "I have a table that has a set number of rows per customer (i.e. 20). I'd like to pivot that so that each customer has those values as COLUMNS returned. For example:
Customer
1 Value1
1 Value2
1 Value3
2 Value1
2 Value2
2 Value3
3 Value1
3 Value2
3 Value3

I'd like to turn it into:
1 Value1 Value2 Value3
2 Value1 Value2 Value3
3 Value1 Value2 Value3

Any ideas? I thought of a temp table with 20 columns and looping for each customer to see if a value has been set and if not that's the one to set, but it sounds so clugy.

Thanx!"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-31 : 08:54:15

select cust ,
(select top 1 val from tbl t2 where t1.cust = t2.cust order by val) ,
(select top 1 val from (select top 2 val from tbl t2 where t1.cust = t2.cust order by val) a order by val desc) ,
(select top 1 val from (select top 3 val from tbl t2 where t1.cust = t2.cust order by val) a order by val desc) ,
from tbl t1
group by cust

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
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