多个列上的SQL Server数据透视表 [英] SQL server Pivot on Multiple Columns

问题描述

我正在尝试在多列上进行透视.我正在使用SQL Server2008.这是到目前为止我尝试过的内容

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

以下是我得到的结果

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.

推荐答案

我首先将各列成对旋转，然后将它们旋转.基本上，取消透视处理会将列对(rscd，position和accd，aposition)转换为行，然后可以应用透视.该代码将是:

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;

请参见带有演示的SQL小提琴

这篇关于多个列上的SQL Server数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！