T-SQL: PIVOT Operator with Dynamic Columns

In My Previous post "PIVOT Operator in SQL", I explained about, How to use T-SQL Operator PIVOT.

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!