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

Well I guess I am really dense. I have read the stuff on MS pivot tables and I am still having problems getting this correct. I have seen some of you that seem to eat and sleep this stuff, so I just decided to sign-up and ask the question.

嗯，我想我真的很迟钝。我已经读过了关于数据透视表的东西，我仍然有一些问题。我看到你们中有些人似乎在吃和睡这些东西，所以我决定注册并问这个问题。

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.

把数字放在一边，在顶部放几周。

Thanks for the help.

谢谢你的帮助。

7 个解决方案

#1

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

如果您正在使用SQL Server 2005+，那么您可以使用PIVOT函数将数据从行转换为列。

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.

如果周数未知，您似乎需要使用动态sql，但是使用硬编码版本更容易看到正确的代码。

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;

See SQL Demo

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

See SQL Demo.

查看演示SQL。

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 |

#2

This is for dynamic # of weeks.

这是动态的周数。

Full example here:SQL Dynamic Pivot

这里的完整示例:SQL Dynamic 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

#3

I've achieved the same thing before by using subqueries. So if your original table was called StoreCountsByWeek, and you had a separate table that listed the Store IDs, then it would look like this:

我以前通过使用子查询实现了同样的功能。如果你的原始表被称为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

One advantage to this method is that the syntax is more clear and it makes it easier to join to other tables to pull other fields into the results too.

这种方法的一个优点是语法更清晰，并且更容易连接到其他表，从而将其他字段也拖到结果中。

My anecdotal results are that running this query over a couple of thousand rows completed in less than one second, and I actually had 7 subqueries. But as noted in the comments, it is more computationally expensive to do it this way, so be careful about using this method if you expect it to run on large amounts of data .

我的观察结果是，在不到一秒的时间内，在几千行上运行这个查询，实际上我有7个子查询。但是正如注释中所指出的那样，这样做要花费更多的计算成本，所以如果您希望它在大量数据上运行，那么请小心使用这种方法。

#4

This is what you can do:

这就是你能做的:

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

DEMO

演示

#5

I'm writing an sp that could be useful for this purpose, basically this sp pivot any table and return a new table pivoted or return just the set of data, this is the way to execute it:

我正在编写一个sp，它可以在这个目的上有用，基本上这个sp枢轴指向任何一个表，返回一个新的表，或者只返回一组数据，这就是执行它的方式:

Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg='sum([column_to_agg]),avg([another_column_to_agg]),',
        @sel_cols='column_to_select1,column_to_select2,column_to_select1',@new_table=returned_table_pivoted;

please note that in the parameter @agg the column names must be with '[' and the parameter must end with a comma ','

请注意，在参数@agg中，列名必须为“[”，参数必须以逗号结束。

SP

SP

Create Procedure [dbo].[rs_pivot_table]
    @schema sysname=dbo,
    @table sysname,
    @column sysname,
    @agg nvarchar(max),
    @sel_cols varchar(max),
    @new_table sysname,
    @add_to_col_name sysname=null
As
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
Begin

    Declare @query varchar(max)='';
    Declare @aggDet varchar(100);
    Declare @opp_agg varchar(5);
    Declare @col_agg varchar(100);
    Declare @pivot_col sysname;
    Declare @query_col_pvt varchar(max)='';
    Declare @full_query_pivot varchar(max)='';
    Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica

    Create Table #pvt_column(
        pivot_col varchar(100)
    );

    Declare @column_agg table(
        opp_agg varchar(5),
        col_agg varchar(100)
    );

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
        Set @ind_tmpTbl=0;
    ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
        Set @ind_tmpTbl=1;

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OR 
        OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL
    Begin
        Set @query='DROP TABLE '+@new_table+'';
        Exec (@query);
    End;

    Select @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';
    Print @query;

    Insert into #pvt_column(pivot_col)
    Exec (@query)

    While charindex(',',@agg,1)>0
    Begin
        Select @aggDet=Substring(@agg,1,charindex(',',@agg,1)-1);

        Insert Into @column_agg(opp_agg,col_agg)
        Values(substring(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));

        Set @agg=Substring(@agg,charindex(',',@agg,1)+1,len(@agg))

    End

    Declare cur_agg cursor read_only forward_only local static for
    Select 
        opp_agg,col_agg
    from @column_agg;

    Open cur_agg;

    Fetch Next From cur_agg
    Into @opp_agg,@col_agg;

    While @@fetch_status=0
    Begin

        Declare cur_col cursor read_only forward_only local static for
        Select 
            pivot_col 
        From #pvt_column;

        Open cur_col;

        Fetch Next From cur_col
        Into @pivot_col;

        While @@fetch_status=0
        Begin

            Select @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+
            ' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+
                (case when @add_to_col_name is null then space(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'
            print @query_col_pvt
            Select @full_query_pivot=@full_query_pivot+@query_col_pvt+', '

            --print @full_query_pivot

            Fetch Next From cur_col
            Into @pivot_col;        

        End     

        Close cur_col;
        Deallocate cur_col;

        Fetch Next From cur_agg
        Into @opp_agg,@col_agg; 
    End

    Close cur_agg;
    Deallocate cur_agg;

    Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);

    Select @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
    @schema+'.'+@table+' Group by '+@sel_cols+';';

    print @query;
    Exec (@query);

End;
GO

This is an example of execution:

这是执行的一个例子:

Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;

then Select * From ##TEMPORAL1PVT would return:

然后从##TEMPORAL1PVT中选择*将返回:

#6

select * from (select name, ID from Empoyee) Visits
    pivot(sum(ID) for name
    in ([Emp1],
    [Emp2],
    [Emp3]
    ) ) as pivottable;

#7

Here is a revision of @Tayrn answer above that might help you understand pivoting a little easier:

下面是对@Tayrn回答的修改，可能会帮助你更容易理解pi投票:

This may not be the best way to do this, but this is what helped me wrap my head around how to pivot tables.

这可能不是最好的方法，但是这帮助我思考如何使用数据透视表。

ID = rows you want to pivot

ID =要透视的行

MY_KEY = the column you are selecting from your original table that contains the column names you want to pivot.

MY_KEY =从原始表中选择的列，该列包含您想要透视的列名。

VAL = the value you want returned under each column.

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

MAX(VAL) => Can be replaced with other aggregiate functions. SUM(VAL), MIN(VAL), ETC...

MAX(VAL) =>可以替换为其他的aggregiate函数。总和(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);

执行(@query);