SQL server 在多列上的 Pivot [英] SQL server Pivot on Multiple Columns

问题描述

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！