Geek Answers Handbook

SQL Server Pivot on multiple columns

I am trying to rotate a few 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

Below is what I get

 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 ACBD

Any help? what is wrong in my code.

+7
sql sql-server pivot
source share
3 answers

At first I would not unlock the columns, and then rotate them. Basically, the univot process converts pairs of columns ( rscd , position and accd , aposition ) into rows, then you can apply a support element. The code will look like this:

 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;

See SQL Fiddle with Demo

+11
source share
 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
+4
source share

Do not use an identification column. Use the view to retrieve all columns except the identifier, and then use the PIVOT table.

-3
source share

More articles:


All Articles