在SQL Server中使用“数据透视表"将行转换为列 [英] Convert Rows to columns using 'Pivot' in SQL Server

问题描述

我已经阅读了MS数据透视表上的内容，但仍然无法获得正确的答案.

I have read the stuff on MS pivot tables and I am still having problems getting this correct.

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

I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.

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

我希望它以数据透视表的形式出现，如下所示:

I would like it to come out as a pivot table, like this:

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

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

Store numbers down the side and weeks across the top.

推荐答案

如果使用的是SQL Server 2005+，则可以使用PIVOT函数将数据从行转换为列.

If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.

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

It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

首先，这里是一些快速的表定义和使用数据:

First up, here are some quick table definitions and data for use:

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);

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

If your values are known, then you will hard-code the query:

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

请参见 SQL演示

然后，如果您需要动态生成星期数，则代码将是:

Then if you need to generate the week number dynamically, your code will be:

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数字列表.两者给出相同的结果:

The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:

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

这篇关于在SQL Server中使用“数据透视表"将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！