SQL 查询动态 PIVOT [英] SQL Query Dynamic PIVOT

问题描述

可能的重复:
SQL Server 动态 PIVOT 查询?

我有以下结构的临时表:

I have temporary table with following structure:

MONTH  ID           CNT
-----  -----------  ---
4      TOTAL_COUNT  214
5      TOTAL_COUNT  23
6      TOTAL_COUNT  23
4      FUNC_COUNT   47
5      FUNC_COUNT   5
6      FUNC_COUNT   5
4      INDIL_COUNT  167
5      INDIL_COUNT  18
6      INDIL_COUNT  18

我如何在此表中获得超过一个月的数据透视表，例如:

How i can get the Pivot over month in this table like:

ID           APRIL  MAY  JUNE 
-----------  -----  ---  ----
TOTAL_COUNT  214    23   23
FUNC_COUNT   47     5    5
INDIL_COUNT  167    18   18

请考虑这种表格格式.我在发布这种格式时有点乱.

Please consider this table format. I am little messy in posting this format.

推荐答案

虽然您可以使用 Static Pivot - 您可以对月份进行硬编码.在评论中，您表示月数可能未知，如果是这种情况，那么您将需要使用 Dynamic Pivot 来生成月列表.使用 Dynamic Pivot 可让您灵活地在运行之前不知道所需的列.

While you can use a Static Pivot - one that you hard-code the months. In the comments, you stated that the number of months maybe be unknown, if that is the case then you will want to use a Dynamic Pivot to generate the list of months. Using a Dynamic Pivot gives you the flexibility of not knowing the columns you need until you run it.

create table t
( 
    [month] int, 
    [id] nvarchar(20), 
    [cnt] int 
)

insert t values (4,'TOTAL_COUNT',214)
insert t values (5,'TOTAL_COUNT',23)
insert t values (6,'TOTAL_COUNT',23)
insert t values (4,'FUNC_COUNT',47)
insert t values (5,'FUNC_COUNT',5)
insert t values (6,'FUNC_COUNT',5)
insert t values (4,'INDIL_COUNT',167)
insert t values (5,'INDIL_COUNT',18)
insert t values (6,'INDIL_COUNT',18)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
            FROM t 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' from 
            (
                select month, id, cnt
                from t
           ) x
            pivot 
            (
                 sum(cnt)
                for month in (' + @cols + ')
            ) p '


execute(@query)

drop table t

结果是:

这篇关于SQL 查询动态 PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！