SQL Server 动态 PIVOT 查询?

SQL Server dynamic PIVOT query?

提问人:Sean Cunningham 提问时间:5/2/2012 最后编辑:LaxmiSean Cunningham 更新时间:9/23/2023 访问量:333425

问:

我的任务是想出一种翻译以下数据的方法:

date        category        amount
1/1/2012    ABC             1000.00
2/1/2012    DEF             500.00
2/1/2012    GHI             800.00
2/10/2012   DEF             700.00
3/1/2012    ABC             1100.00

转换为以下内容:

date        ABC             DEF             GHI
1/1/2012    1000.00
2/1/2012                    500.00
2/1/2012                                    800.00
2/10/2012                   700.00
3/1/2012    1100.00

空白点可以是 NULL 或空白,两者都很好,并且类别需要是动态的。另一个可能的警告是,我们将在有限的容量中运行查询,这意味着临时表已用完。我试图研究并登陆,但由于我以前从未使用过它,我真的不理解它,尽管我尽了最大努力来弄清楚它。谁能为我指出正确的方向?PIVOT

sql-server t-sql 透视

评论


答:

308赞 Taryn 5/2/2012 #1

动态 SQL PIVOT:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
    
set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

结果:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

评论

0赞 Nate Anderson 10/3/2015
所以 \@cols 必须是字符串连接的,对吧?我们不能使用 sp_executesql 和参数绑定来插值 \@cols 吗?即使我们自己构造 \@cols,如果它以某种方式包含恶意 SQL。在连接它并执行它之前,我可以采取任何其他缓解步骤吗?
0赞 Patrick 11/30/2016
您将如何对此的行和列进行排序?
0赞 Taryn 11/30/2016
@PatrickSchomburg 有多种方法 - 如果你想对 排序,那么你可以删除 并使用 和 当你得到 .@colsDISTINCTGROUP BYORDER BY@cols
0赞 akd 11/30/2016
我有类似的问题,但我没有CategoryId。类别名称来自不同的表。那么,是否可以从另一个表中读取列标题呢?
0赞 Taryn 11/30/2016
@akd 如果我正确理解了问题,您应该能够做到这一点。您只需联接 PIVOT 中的表,并在获取 .@cols
3赞 m0rg4n 12/21/2015 #2

我的解决方案是清理不必要的空值

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

select @cols = STUFF((SELECT ',' + QUOTENAME(CodigoFormaPago) 
                from PO_FormasPago
                order by CodigoFormaPago
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

select @maxcols = STUFF((SELECT ',MAX(' + QUOTENAME(CodigoFormaPago) + ') as ' + QUOTENAME(CodigoFormaPago)
                from PO_FormasPago
                order by CodigoFormaPago
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

set @query = 'SELECT CodigoProducto, DenominacionProducto, ' + @maxcols + '
            FROM
            (
                SELECT 
                CodigoProducto, DenominacionProducto,
                ' + @cols + ' from 
                 (
                    SELECT 
                        p.CodigoProducto as CodigoProducto,
                        p.DenominacionProducto as DenominacionProducto,
                        fpp.CantidadCuotas as CantidadCuotas,
                        fpp.IdFormaPago as IdFormaPago,
                        fp.CodigoFormaPago as CodigoFormaPago
                    FROM
                        PR_Producto p
                        LEFT JOIN PR_FormasPagoProducto fpp
                            ON fpp.IdProducto = p.IdProducto
                        LEFT JOIN PO_FormasPago fp
                            ON fpp.IdFormaPago = fp.IdFormaPago
                ) xp
                pivot 
                (
                    MAX(CantidadCuotas)
                    for CodigoFormaPago in (' + @cols + ')
                ) p 
            )  xx 
            GROUP BY CodigoProducto, DenominacionProducto'

t @query;

execute(@query);
41赞 mkdave99 7/21/2016 #3

动态 SQL PIVOT

创建列字符串的不同方法

create table #temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select date, amount, category from #temp
) src
pivot 
(
    max(amount) for category in (' + @cols + ')
) piv'

execute(@query)
drop table #temp

结果

date                    ABC     DEF     GHI
2012-01-01 00:00:00.000 1000.00 NULL    NULL
2012-02-01 00:00:00.000 NULL    500.00  800.00
2012-02-10 00:00:00.000 NULL    700.00  NULL
2012-03-01 00:00:00.000 1100.00 NULL    NULL
3赞 Arockia Nirmal 2/27/2017 #4

下面的代码提供了在输出中将 NULL 替换为的结果。

表创建和数据插入:

create table test_table
 (
 date nvarchar(10),
 category char(3),
 amount money
 )

 insert into test_table values ('1/1/2012','ABC',1000.00)
 insert into test_table values ('2/1/2012','DEF',500.00)
 insert into test_table values ('2/1/2012','GHI',800.00)
 insert into test_table values ('2/10/2012','DEF',700.00)
 insert into test_table values ('3/1/2012','ABC',1100.00)

查询以生成确切的结果,该结果也将 NULL 替换为零:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(category) + ', 0) AS '
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT date, ' + @PivotSelectColumnNames + '
FROM test_table
pivot(sum(amount) for category in (' + @PivotColumnNames + ')) as pvt';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

输出:

enter image description here

23赞 SFrejofsky 7/13/2017 #5

我知道这个问题比较老,但我正在寻找答案,并认为我可能能够扩展问题的“动态”部分,并可能帮助某人。

首先,我构建这个解决方案是为了解决几个同事遇到的一个问题,即需要快速调整的不稳定和大型数据集。

此解决方案需要创建一个存储过程,因此,如果这不符合您的需求,请立即停止阅读。

此过程将采用数据透视表的关键变量,以便为不同的表、列名和聚合动态创建数据透视表。Static 列用作透视表的 group by / identity 列(如果不需要,可以从代码中删除,但在透视表语句中很常见,并且是解决原始问题所必需的),透视列是生成最终结果列名称的位置,value 列是聚合将应用于的内容。Table 参数是包含架构 (schema.tablename) 的表的名称,这部分代码可能会使用一些爱,因为它不像我希望的那样干净。它对我有用,因为我的使用不是公开的,而且 sql 注入不是问题。Aggregate 参数将接受任何标准的 sql 聚合 'AVG'、'SUM'、'MAX' 等。该代码还默认为 MAX 作为聚合,这不是必需的,但最初为其构建的受众不了解透视,通常使用 max 作为聚合。

让我们从创建存储过程的代码开始。此代码应该适用于所有版本的 SSMS 2005 及更高版本,但我没有在 2005 年或 2016 年对其进行测试,但我不明白为什么它不起作用。

create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
    (
        @STATIC_COLUMN VARCHAR(255),
        @PIVOT_COLUMN VARCHAR(255),
        @VALUE_COLUMN VARCHAR(255),
        @TABLE VARCHAR(255),
        @AGGREGATE VARCHAR(20) = null
    )

AS


BEGIN

SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
        @SQLSTRING NVARCHAR(MAX),
        @PIVOT_SQL_STRING NVARCHAR(MAX),
        @TEMPVARCOLUMNS NVARCHAR(MAX),
        @TABLESQL NVARCHAR(MAX)

if isnull(@AGGREGATE,'') = '' 
    begin
        SET @AGGREGATE = 'MAX'
    end


 SET @PIVOT_SQL_STRING =    'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                            FROM '+@TABLE+'
                            WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
                            FOR XML PATH(''''), TYPE)
                            .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                            from '+@TABLE+' ma
                            ORDER BY ' + @PIVOT_COLUMN + ''

declare @TAB AS TABLE(COL NVARCHAR(MAX) )

INSERT INTO @TAB EXEC SP_EXECUTESQL  @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 

SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)


SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')


SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')  
                    INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

                    select * from (
                    SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a

                    PIVOT
                    (
                    '+@AGGREGATE+'('+@VALUE_COLUMN+')
                    FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
                    ) piv

                    SELECT * FROM @RETURN_TABLE'



EXEC SP_EXECUTESQL @SQLSTRING

END

接下来,我们将为示例准备好数据。我从公认的答案中获取了数据示例,并添加了几个数据元素以在此概念验证中使用,以显示聚合变化的不同输出。

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00) -- added
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00) -- added
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00) -- addded
insert into temp values ('3/1/2012', 'ABC', 1100.00)

以下示例显示了各种执行语句,其中显示了各种聚合作为简单示例。我没有选择更改静态、透视和值列以保持示例简单。您应该能够复制并粘贴代码以开始自己处理它

exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','max'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','avg'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','min'

此执行分别返回以下数据集。

enter image description here

评论

1赞 Przemyslaw Remin 9/28/2017
干得好!您能否选择TVF而不是存储过程。从这样的TVF中进行选择会很方便。
4赞 SFrejofsky 9/29/2017
不幸的是,据我所知,没有,因为你不能有一个动态的TVF结构。TVF 中必须有一组静态列。
20赞 nvogel 1/25/2019 #6

SQL Server 2017 的更新版本使用 STRING_AGG 函数构造数据透视列列表:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
);

insert into temp values ('20120101', 'ABC', 1000.00);
insert into temp values ('20120201', 'DEF', 500.00);
insert into temp values ('20120201', 'GHI', 800.00);
insert into temp values ('20120210', 'DEF', 700.00);
insert into temp values ('20120301', 'ABC', 1100.00);


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

SET @cols = (SELECT STRING_AGG(category,',') FROM (SELECT DISTINCT category FROM temp WHERE category IS NOT NULL)t);

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p ';

execute(@query);

drop table temp;

评论

1赞 Ian Kemp 7/16/2020
不幸的是,这比@mkdave99的答案要痛苦得多。首先,如果你在构建数据透视列列表时需要对它进行排序,你需要记住包含偏移量 0 行的愚蠢的 MSSQL 技巧。其次,您还必须记住额外的愚蠢的MSSQL黑客,包括不必要的表别名。第三,它也比@mkdave99的回答慢了一点。t
5赞 Charlieface 9/2/2021
@IanKemp 要对行进行排序,必须放在查询的外部,无需黑客操作。 在子查询中,即使 Just 不执行您认为它执行的操作,它也仅用于计算偏移量以及从子查询返回的行,而不是它们可能返回的顺序。始终使用表别名,它们使代码更具可读性,不知道为什么您认为它们没有必要。MKDAVE99 的答案使用可变合并,这是不可靠的,并且可能会给出不正确的结果,尤其是在存在 See dba.stackexchange.com/a/132709/220697ORDER BYORDER BYOFFSET 0ORDER BY
0赞 Krish KvR 10/27/2021 #7
CREATE TABLE #PivotExample(
   [ID]      [nvarchar](50)   NULL,       
   [Description]   [nvarchar](50)   NULL,
   [ClientId]   [smallint] NOT NULL,
)
GO


INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc1',1008)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc2',2000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc3',3000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc4',4000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc1',5000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc2',6000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc3', 7000)

SELECT * FROM #PivotExample




--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
 
--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([Description])
FROM (SELECT DISTINCT [Description] FROM [dbo].#PivotExample) AS PivotExample
 
--SELECT   @PivotColumns
 
--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N' -- Your pivoted result comes here
    SELECT ID, ' + @PivotColumns + '
    FROM 
    (
        -- Source table should in a inner query
        SELECT ID,[Description],[ClientId]
        FROM #PivotExample
    )AS P
    PIVOT
    (     
          -- Select the values from derived table P
          SUM(ClientId) 
          FOR [Description] IN (' + @PivotColumns + ') 
    )AS PVTTable'
 
--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery


Drop table #PivotExample
0赞 Mark Z. 2/15/2022 #8

完全通用的方式,可以在非传统的 MS SQL 环境(例如 Azure Synapse Analytics 无服务器 SQL 池)中工作 - 它在 SPROC 中,但无需这样使用......

-- DROP PROCEDURE IF EXISTS
if object_id('dbo.usp_generic_pivot') is not null 
     DROP PROCEDURE dbo.usp_generic_pivot
GO;

CREATE PROCEDURE dbo.usp_generic_pivot (
    @source NVARCHAR (100), -- table or view object name
   @pivotCol NVARCHAR (100), -- the column to pivot
   @pivotAggCol NVARCHAR (100), -- the column with the values for the pivot
   @pivotAggFunc NVARCHAR (20), -- the aggregate function to apply to those values
   @leadCols NVARCHAR (100) -- comma seprated list of other columns to keep and order by
)
AS
BEGIN
   DECLARE @pivotedColumns NVARCHAR(MAX)
   DECLARE @tsql NVARCHAR(MAX)

   SET @tsql = CONCAT('SELECT @pivotedColumns = STRING_AGG(qname, '','') FROM (SELECT DISTINCT QUOTENAME(', @pivotCol,') AS qname FROM ',@source, ') AS qnames')
   EXEC sp_executesql @tsql, N'@pivotedColumns nvarchar(max) out', @pivotedColumns out

   SET @tsql = CONCAT ( 'SELECT ', @leadCols,   ',', @pivotedColumns,' FROM ',' ( SELECT ',@leadCols,',',
         @pivotAggCol,',',   @pivotCol,   ' FROM ',   @source,   ') as t ',
         ' PIVOT (', @pivotAggFunc,   '(', @pivotAggCol,   ')',' FOR ',   @pivotCol,
         '   IN (', @pivotedColumns,')) as pvt ',' ORDER BY ',   @leadCols)

   EXEC (@tsql)

END
GO;

-- TEST EXAMPLE
EXEC dbo.usp_generic_pivot  
    @source  = '[your_db].[dbo].[form_answers]',
   @pivotCol  = 'question', 
   @pivotAggCol   = 'answer',
   @pivotAggFunc  = 'MAX', 
   @leadCols    = 'candidate_id, candidate_name' 
GO;
2赞 Paul White #9

Taryn 的回答版本,性能有所改进:

数据

CREATE TABLE dbo.Temp
(
    [date] datetime NOT NULL,
    category nchar(3) NOT NULL,
    amount money NOT NULL,

    INDEX [CX dbo.Temp date] CLUSTERED ([date]),
    INDEX [IX dbo.Temp category] NONCLUSTERED (category)
);

INSERT dbo.Temp
    ([date], category, amount)
VALUES
    ({D '2012-01-01'}, N'ABC', $1000.00),
    ({D '2012-01-02'}, N'DEF', $500.00),
    ({D '2012-01-02'}, N'GHI', $800.00),
    ({D '2012-02-10'}, N'DEF', $700.00),
    ({D '2012-03-01'}, N'ABC', $1100.00);

动态枢轴

DECLARE 
    @Delimiter nvarchar(4000) = N',',
    @DelimiterLength bigint,
    @Columns nvarchar(max),
    @Query nvarchar(max);

SET @DelimiterLength = LEN(REPLACE(@Delimiter, SPACE(1), N'#'));

-- Before SQL Server 2017
SET @Columns =
    STUFF
    (
        (
            SELECT 
                [text()] = @Delimiter,
                [text()] = QUOTENAME(T.category)
            FROM dbo.Temp AS T
            WHERE T.category IS NOT NULL
            GROUP BY T.category
            ORDER BY T.category
            FOR XML PATH (''), TYPE
        )
        .value(N'text()[1]', N'nvarchar(max)'),
        1, @DelimiterLength, SPACE(0)
    );

-- Alternative for SQL Server 2017+ and database compatibility level 110+
SELECT @Columns = 
    STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(T.category)), N',')
        WITHIN GROUP (ORDER BY T.category)
FROM 
(
    SELECT T2.category
    FROM dbo.Temp AS T2
    WHERE T2.category IS NOT NULL
    GROUP BY T2.category
) AS T;

IF @Columns IS NOT NULL
BEGIN
    SET @Query = 
        N'SELECT [date], ' + 
        @Columns + 
        N' 
        FROM
        (
            SELECT [date], amount, category
            FROM dbo.Temp
        ) AS S
        PIVOT
        (
            MAX(amount)
            FOR category IN (' +
            @Columns +
            N')
        ) AS P;';

    EXECUTE sys.sp_executesql @Query;
END;

执行计划

dynamic pivot execution plans

结果

日期 美国广播公司 DEF的 GHI公司
2012-01-01 00:00:00.000 1000.00
2012-01-02 00:00:00.000 500.00 800.00
2012-02-10 00:00:00.000 700.00
2012-03-01 00:00:00.000 1100.00