在SQL Server中使用“数据透视表”将行转换为列

小编典典

在SQL Server中使用“数据透视表”将行转换为列

sql

我已经阅读了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

将数字存储在侧面，数周存储在顶部。

阅读 180

2021-05-05

共1个答案

小编典典

如果使用的是SQL Server 2005+，则可以使用该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 |

2021-05-05