SQL Server动态PIVOT查询?

动态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

这就是我的解决方案，清除不必要的空值

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

动态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

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

表创建和数据插入:

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

输出:

我知道这个问题已经很老了，但我正在看答案，并认为我可能能够扩展问题的“动态”部分，并可能帮助别人。

首先，我构建这个解决方案是为了解决几个同事遇到的需要快速旋转的不恒定和大型数据集的问题。

这个解决方案需要创建一个存储过程，所以如果这对您的需求来说是不可能的，请停止阅读。

这个过程将采用枢轴语句的关键变量来动态地为不同的表、列名和聚合创建枢轴语句。Static列用作枢轴的group by / identity列(如果没有必要，可以从代码中剥离出来，但在枢轴语句中非常常见，并且是解决原始问题所必需的)，枢轴列是生成最终结果列名的地方，而值列是将应用聚合的地方。Table参数是包含模式(schema.tablename)的表的名称，这部分代码可能需要一些爱，因为它不像我希望的那样干净。它为我工作，因为我的使用不是公开的，sql注入不是一个问题。聚合参数将接受任何标准的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'

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

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;

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

完全通用的方式，将在非传统的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;

具有性能改进的世界的回答版本:

数据

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;

执行计划

结果