Server数据透视多列

Question

我有下表

c1  b1  y1  sk  sales   cogs
in  b2b 2012    s1  1000    900
in  b2b 2012    s2  1100    1050
in  b2b 2011    s1  1000    900

我想转到多列上。我尝试了几次，但无法生成以下的输出。

c1  b1  sk  sales_2012  sales_2011  cogs_2012   cogs_2011
in  b2b s1  1000         1000            900    900
in  b2b s2  1100                     1050   

请帮帮忙

提前感谢

Answer 1

你可以很容易地用一个联合在一个枢轴上。

Select *
FROM
(SELECT c1,  b1, 'sales_' + CAST(y1 AS VARCHAR(4)) AS Dimension,  sk,  sales AS Value
  FROM dbo.s1
  UNION ALL
  SELECT c1,  b1, 'cogs_' + CAST(y1 AS VARCHAR(4)) AS Dimension,  sk,  cogs AS Value
  FROM dbo.s1
   ) AS p
  PIVOT
  (
  SUM(Value)
  FOR Dimension IN
  ( sales_2012, sales_2011  , cogs_2012   , cogs_2011)
  ) AS pvt