Help needed in pivoting (SQL Server 2005)
- by Newbie
I have a table like
ID Grps Vals
--- ---- -----
1 1 1
1 1 3
1 1 45
1 2 23
1 2 34
1 2 66
1 3 10
1 3 17
1 3 77
2 1 144
2 1 344
2 1 555
2 2 11
2 2 22
2 2 33
2 3 55
2 3 67
2 3 77
The desired output being
ID Record1 Record2 Record3
--- ------- ------- -------
1 1 23 10
1 3 34 17
1 45 66 77
2 144 11 55
2 344 22 67
2 555 33 77
I have tried(using while loop) but the program is running slow. I have been asked to do so by using SET based approach. My approach so far is
SELECT ID,[1] AS [Record1], [2] AS [Record2], [3] as [Record3]
FROM (
Select
Row_Number() Over(Partition By ID Order By Vals) records
,*
From myTable)x
PIVOT
(MAX(vals) FOR Grps IN ([1],[2],[3])) p
But it is not working.
Can any one please help to solve this.(SQL SERVER 2005)
