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

我读过关于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

店铺编号写在边上，星期写在上面。

当前回答

这是动态的# of weeks。

完整示例:SQL动态Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Store, ' + @ColumnName + ' 
    FROM #StoreSales
    PIVOT(SUM(xCount) 
          FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

2014-12-17 18:43:13

其他回答

这是动态的# of weeks。

完整示例:SQL动态Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Store, ' + @ColumnName + ' 
    FROM #StoreSales
    PIVOT(SUM(xCount) 
          FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

2014-12-17 18:43:13

以下是上面@Tayrn回答的修订版，可能会帮助你更容易理解旋转:

这可能不是最好的方法，但这帮助我理解了如何透视表。

ID =要主元的行数

MY_KEY =您要从原始表中选择的列，其中包含您想要进行主元转换的列名。

VAL =您希望在每列下返回的值。

MAX(VAL) =>可替换为其他聚合函数。Sum (val)， min (val)，等等…

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(MY_KEY) 
                from yt
                group by MY_KEY
                order by MY_KEY ASC
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
set @query = 'SELECT ID,' + @cols + ' from 
         (
            select ID, MY_KEY, VAL 
            from yt
        ) x
        pivot 
        (
            sum(VAL)
            for MY_KEY in (' + @cols + ')
        ) p '

        execute(@query);

2018-04-19 21:01:34

你可以这样做:

SELECT * 
FROM yourTable
PIVOT (MAX(xCount) 
       FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt

DEMO

2013-04-10 16:46:20

我以前通过使用子查询实现了同样的目的。所以如果你原来的表叫做sto叙述byweek，你有一个单独的表列出商店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个子查询。但是正如评论中所指出的，这样做的计算成本更高，所以如果您希望它在大量数据上运行，请谨慎使用此方法。

2016-02-03 19:16:58

Pivot是SQL操作符之一，用于将输出中的唯一数据从一列转换为多列。这也意味着将行转换为列(旋转表)。让我们考虑这张桌子，

如果我想根据每个客户的产品类型(扬声器、眼镜、耳机)过滤这些数据，那么使用Pivot操作符。

Select CustmerName, Speaker, Glass, Headset  
from TblCustomer  
   Pivot  
    (  
     Sum(Price) for Product in ([Speaker],[Glass],[Headset])  
    ) as PivotTable

2021-11-16 11:23:14

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

推荐文章

最新文章

标签