SQL Server dynamic PIVOT query?

Dynamic SQL PIVOT

Different approach for creating columns string

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 

Result

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 

I know this question is older but I was looking thru the answers and thought that I might be able to expand on the "dynamic" portion of the problem and possibly help someone out.

First and foremost I built this solution to solve a problem a couple of coworkers were having with inconstant and large data sets needing to be pivoted quickly.

This solution requires the creation of a stored procedure so if that is out of the question for your needs please stop reading now.

This procedure is going to take in the key variables of a pivot statement to dynamically create pivot statements for varying tables, column names and aggregates. The Static column is used as the group by / identity column for the pivot(this can be stripped out of the code if not necessary but is pretty common in pivot statements and was necessary to solve the original issue), the pivot column is where the end resultant column names will be generated from, and the value column is what the aggregate will be applied to. The Table parameter is the name of the table including the schema (schema.tablename) this portion of the code could use some love because it is not as clean as I would like it to be. It worked for me because my usage was not publicly facing and sql injection was not a concern. The Aggregate parameter will accept any standard sql aggregate 'AVG', 'SUM', 'MAX' etc. The code also defaults to MAX as an aggregate this is not necessary but the audience this was originally built for did not understand pivots and were typically using max as an aggregate.

Lets start with the code to create the stored procedure. This code should work in all versions of SSMS 2005 and above but I have not tested it in 2005 or 2016 but I can not see why it would not work.

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 

Next we will get our data ready for the example. I have taken the data example from the accepted answer with the addition of a couple of data elements to use in this proof of concept to show the varied outputs of the aggregate change.

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) 

The following examples show the varied execution statements showing the varied aggregates as a simple example. I did not opt to change the static, pivot, and value columns to keep the example simple. You should be able to just copy and paste the code to start messing with it yourself

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' 

This execution returns the following data sets respectively.

Updated version for SQL Server 2017 using STRING_AGG function to construct the pivot column list:

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; 

A version of Taryn's answer with performance improvements:

Data

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

Dynamic pivot

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; 

Execution plans

Results

The below code provides the results which replaces NULL to zero in the output.

Table creation and data insertion:

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) 

Query to generate the exact results which also replaces NULL with zeros:

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 

OUTPUT :

There's my solution cleaning up the unnecesary null values

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

Fully generic way that will work in non-traditional MS SQL environments (e.g. Azure Synapse Analytics Serverless SQL Pools) - it's in a SPROC but no need to use as such...

-- 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 #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