Grouped Rows to Columns - SQL Server Forums

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)

Grouped Rows to Columns

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2008-07-10 : 13:06:25

Hi,

Here is my table


Col1  Col2  Col3
----  ----  ----
1     ST    100
1     CT    545
2     CT    200
3     ST    150
3     BT    130
3     AT    350
3     GT    950

I need the following output


Col1  Col2  Col3  Col4  Col5  Col6  Col7  Col8  Col9
----  ----  ----  ----  ----  ----  ----  ----  ----
1     ST    100   CT    545   Null  Null  Null  Null
2     CT    200   Null  Null  Null  Null  Null  Null
3     ST    150   BT    130   AT    350   GT    950

Thanks

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-10 : 21:59:02

[code]DECLARE @TABLE TABLE
(
Col1 int,
Col2 CHAR(2),
Col3 int
)
INSERT INTO @TABLE
SELECT 1, 'ST', 100 UNION ALL
SELECT 1, 'CT', 545 UNION ALL
SELECT 2, 'CT', 200 UNION ALL
SELECT 3, 'ST', 150 UNION ALL
SELECT 3, 'BT', 130 UNION ALL
SELECT 3, 'AT', 350 UNION ALL
SELECT 3, 'GT', 950

SELECT t.Col1,
Col2 = MAX(CASE WHEN row_no = 1 THEN t.Col2 END),
Col3 = MAX(CASE WHEN row_no = 1 THEN t.Col3 END),
Col4 = MAX(CASE WHEN row_no = 2 THEN t.Col2 END),
Col5 = MAX(CASE WHEN row_no = 2 THEN t.Col3 END),
Col6 = MAX(CASE WHEN row_no = 3 THEN t.Col2 END),
Col7 = MAX(CASE WHEN row_no = 3 THEN t.Col3 END),
Col8 = MAX(CASE WHEN row_no = 4 THEN t.Col2 END),
Col9 = MAX(CASE WHEN row_no = 4 THEN t.Col3 END)
FROM
(
SELECT Col1, Col2, Col3, row_no = (SELECT COUNT(*) FROM @TABLE x WHERE x.Col1 = t.Col1 AND x.Col2 <= t.Col2)
FROM @TABLE t
) t
GROUP BY t.Col1
/*
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
----------- ---- ----------- ---- ----------- ---- ----------- ---- -----------
1 CT 545 ST 100 NULL NULL NULL NULL
2 CT 200 NULL NULL NULL NULL NULL NULL
3 AT 350 BT 130 GT 950 ST 150

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2008-07-11 : 13:11:17

Thanks khtan
It works exactly, as I need.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources