Reputation: 877
I am trying to pivot on multiple columns. I am using SQL server 2008. Here is what I have tried so far
CREATE TABLE #t ( id int, Rscd varchar(10),Accd varchar(10),position int)
INSERT INTO #t Values (10,'A','B',1)
INSERT INTO #t Values (10,'C','D',2)
Select id,[1],[2],[11],[12] FROM
(SELECT id, Rscd,Accd, position , position +10 as Aposition
From #t)
As query
PIVOT (MAX(Rscd )
FOR Position IN ([1],[2])) AS Pivot1
PIVOT (MAX(Accd )
FOR Aposition IN ([11],[12])) AS Pivot2
The below indicated is the result that I am getting
id 1 2 11 12
10 NULL C NULL D
10 A NULL B NULL
But the result that I am trying to achieve is ,
id 1 2 11 12
10 A C B D
Any help ? what is wrong in my code.
Upvotes: 11
Views: 53955
Reputation: 51
Select id,sum([1]),sum([2]),sum([11]),sum([12]) FROM
(SELECT id, Rscd,Accd, position , position +10 as Aposition
From #t)
As query
PIVOT (MAX(Rscd )
FOR Position IN ([1],[2])) AS Pivot1
PIVOT (MAX(Accd )
FOR Aposition IN ([11],[12])) AS Pivot2
group by id
Upvotes: 5
Reputation: 1
Dont Use the ID Column. Use a derived table to retrieve all columns except the ID and then use the PIVOT table.
Upvotes: -3
Reputation: 247690
I would unpivot the columns into pairs first, then pivot them. Basically the unpivot process will convert the pairs of columns (rscd
, position
and accd
, aposition
) into rows, then you can apply the pivot. The code will be:
select id, [1], [2], [11], [12]
from
(
select id, col, value
from #t
cross apply
(
select rscd, position union all
select Accd, position + 10
) c (value, col)
) d
pivot
(
max(value)
for col in ([1], [2], [11], [12])
) piv;
Upvotes: 15