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

Question

使用SQL Server中的“Pivot”将行转换为列我已经阅读了MS数据透视表上的内容，但我仍然遇到问题。我有一个正在创建的临时表，我们会说第1列是商店号，第2列是周数，最后第3列是某种类型的总和。周数也是动态的，商店数是静态的。Store      Week     xCount-------    ----     ------102        1        96101        1        138105        1        37109        1        59101        2        282102        2        212105        2        78109        2        97105        3        60102        3        123101        3        220109        3        87我希望它作为一个数据透视表出来，像这样：Store        1          2          3        4        5        6....----- 101        138        282        220102         96        212        123105         37        109将数字存储在侧面，将数字存储在顶部。

Answer 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) srcpivot(
  sum(xcount)
  for week in ([1], [2], [3])) piv;

请参阅SQL Demo

然后，如果您需要动态生成周数，您的代码将是：

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 Demo。

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

| STORE |   1 |   2 |   3 |

---------------------------

|   101 | 138 | 282 | 220 |

|   102 |  96 | 212 | 123 |

|   105 |  37 |  78 |  60 |

|   109 |  59 |  97 |  87 |

Answer 2

我之前通过使用子查询实现了同样的目标。因此，如果您的原始表名为StoreCountsByWeek，并且您有一个单独的表列出了商店ID，那么它将如下所示：

SELECT StoreID, 

    Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),

    Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),

    Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)

FROM Store

ORDER BY StoreID

这种方法的一个优点是语法更清晰，并且可以更容易地连接到其他表以将其他字段拉入结果中。

我的轶事结果是，在不到一秒的时间内完成了几千行的查询，我实际上有7个子查询。但正如评论中所指出的那样，以这种方式执行它的计算成本更高，因此如果您希望它在大量数据上运行，请小心使用此方法。