Help needed in pivoting (SQL Server 2005)

Question

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)

Answer 1

You were almost there! All I had to do was add the necessary columns to the Partition By and Order By clauses, and it worked:

SELECT  ID,[1] AS [Record1], [2] AS [Record2], [3] as [Record3] 
    FROM (   
    Select  
        Row_Number() Over(Partition By id, grps Order By id, grps, vals) records 
        ,*  
        From myTable)x 
    PIVOT 
  (MAX(vals) FOR Grps IN ([1],[2],[3])) p 

Answer 2

A simpler approach that would not involve the use of PIVOT would something like:

;With ItemGroups As
    (
    Select Id, Grps, Vals 
        , Row_Number() Over ( Partition By Id, Grps Order By Vals ) As RowNum
    From myTable
    )
Select Id
    , Max( Case When Grps = 1 Then Vals End )
    , Max( Case When Grps = 2 Then Vals End )
    , Max( Case When Grps = 3 Then Vals End )
From ItemGroups
Group By Id, RowNum
Order By Id

Answer 3

This may not apply to you but if your using a while loop like....

while( x<Total.length() ){
....do something
}

you should declare Total.length outside of the loop assigned to a variable...

int spoon= Total.length();
while( x< spoon ){
....do something
}

or else

you calculate the number Total.length()every time the loop runs