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
--/*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 +'
FOR [Reporting_Date] IN
) 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.
-- 2. Uncomment when you want to run it.
Enjoy and Happy Coding.