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.