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:


DECLARE @cols AS NVARCHAR(MAX),
    @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)')
        ,1,1,'')
		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],
	  pvt.[Phone_Number],
	  pvt.[SSN],
    ' +    @cols +'
         FROM
    (SELECT [Reporting_Date]
      ,[ID_Number]
      ,[Phone_Number]
      ,[SSN]
      ,[Credit_Score]
	  FROM [Database].[dbo].[vw_Data_Reporting]
	 )
    AS EQ
PIVOT
(
MAX([Credit_Score])
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.
		--execute(@query)

Enjoy and Happy Coding.

Advertisements