在SQL Server中使用“Pivot”将行转换为列

Question

我已经读过关于MS透视表的东西，但我仍然有问题得到正确的这一点。

我有一个正在创建的临时表，我们会说第1列是一个商店号，第2列是一个星期号，最后第3列是某种类型的总数。而且周数是动态的，商店数是静态的。

Store      Week     xCount
-------    ----     ------
102        1        96
101        1        138
105        1        37
109        1        59
101        2        282
102        2        212
105        2        78
109        2        97
105        3        60
102        3        123
101        3        220
109        3        87

我希望它作为一个透视表出来，像这样：

Store        1          2          3        4        5        6....
----- 
101        138        282        220
102         96        212        123
105         37        
109

商店的数字下侧和周横跨顶部。

Answer 1

如果使用的是SQL Server2005+，则可以使用pivot函数将数据从行转换为列。

听起来，如果周数未知，您将需要使用动态sql，但最初使用硬编码版本更容易看到正确的代码。

首先，这里有一些快速的表格定义和数据供使用：

CREATE TABLE #yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO #yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

如果您的值是已知的，那么您将对查询进行硬编码：

select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

请参阅SQL演示

那么如果需要动态生成周号，您的代码将是：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

请参阅SQL演示。

动态版本生成应转换为列的week数字列表。两者都给出了相同的结果：

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |