本文介绍了SQL server 在多列上的 Pivot的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
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.
解决方案
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;
See SQL Fiddle with Demo
这篇关于SQL server 在多列上的 Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！