SQL Puzzle #6

We have a table say myTable and looks like below.

ID Name Sal
1 a         1000
1 a         2000
2 b         3000
2 b         2000
3 c         2000

And need a query to get the result as below.

ID Name Sal1 Sal2
1 a 1000 2000
2 b 3000 2000
3 c 2000 0



Ans:

Declare  @testTable Table
(
ID int,
Name varchar(50),
Sal money
)
Insert into @testTable
Values
(1, 'a', 1000),
(1, 'a', 2000),
(2, 'b', 3000),
(2, 'b', 2000),
(3, 'c', 2000)


select ID,Name,isnull([sal1],0) As [sal1],isnull([sal2],0) As [sal2]
from
(
     select id,name,sal,'Sal'+cast(ROW_NUMBER() over (partition by id,name order by id,name) as varchar(10)) as rownum
     from @testTable
) sources
pivot
(
     sum(sal)
     For rownum in([sal1],[sal2])
) pvt


NB: We can also do it with column dynamically..