SQL Server Dynamic PIVOT Query

Let say you have a table or view that has customers credit scores from every quarter for a few years back and you want to pivot the data out but new data is coming every quarter and you want the query to continue to work.
We need a dynamic pivot query:

    @query  AS NVARCHAR(MAX)

 --Reporting_Date is the move column.
select @cols = STUFF((SELECT  DISTINCT ',' + QUOTENAME(ED.Reporting_Date)
                    FROM [Database].[dbo].[vw_Data_Reporting] AS ED

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
		set @query
		--/*This is the Pivot, we are inserting the @cols in two spots.
		 --  One in the Select from the Pivot
		 --  Two in the FOR IN */
      = 'SELECT pvt.[ID_Number],
    ' +    @cols +'
    (SELECT [Reporting_Date]
	  FROM [Database].[dbo].[vw_Data_Reporting]
    AS EQ
FOR [Reporting_Date]  IN
( '+
@cols +')
) AS pvt
		 --You have to pick PRINT or Execute but only one.

		-- 1. Uncomment to see the Printed Text for what you are about to run.
		--PRINT @query

		-- 2. Uncomment when you want to run it.

Enjoy and Happy Coding.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s