http://blog-mstechnology.blogspot.com/2009/06/pivot-operator-in-sql.html
In Previous post, I explained using Static PIVOT Columns in the Query, which are hard coded.
This post explains with dynamic columns in PIVOT, For Example: Distinct values from columns of a table.
I used the AdventureWorks Database in my samples script
Have a look at this Query, Here I Explained in 4 steps alsong with the Query.
USE AdventureWorks
GO
#1. Declare a Variable to Store All the Column Values
DECLARE @columns VARCHAR(8000)
#2. Columns Vlaues are Storing in @columns Values In ID or QUOTED_ID Format
SELECT
@columns =
COALESCE
(
@columns + ',[' + cast(DATEPART(yyyy, OrderDate) as varchar) + ']',
'[' + cast(DATEPART(yyyy, OrderDate) as varchar)+ ']'
)
FROM
Sales.SalesOrderHeader
GROUP BY
DATEPART(yyyy, OrderDate)
ORDER BY DATEPART(yyyy, OrderDate)
@columns Contains:
#3. Building PIVOT Query with Dynamic Columns(@columns)
DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *FROM
(
SELECT
CustomerID,
DATEPART(yyyy, OrderDate) as OrderYear,
TotalDue
FROM
Sales.SalesOrderHeader
) PIV
PIVOT
(
SUM(TotalDue) FOR OrderYear in (' + @columns + ')
) AS chld'
#4. Executing Built Query
EXECUTE (@query)
GO
Output:
Hope it Helps you!
