SQL server 在多列上的 Pivot

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