Pivot table definition, explaination, How to do a dynamic columned pivot table?

Pivot table does two things:
1. Aggregate on the row fields' values
2. Subcategorizing on the (pivot) column values

Three key elements:
1. row field
2. column field
3. value field

The reason the Pivot table got its name is because you take some or all the values from the column field and rotate those values to become actually columns in the pivot table.

--How to do a dynamic columned pivot table:
--The following query will run on the AdventureWorks DB of the sql 2008R2 sample Database.

DECLARE @cols nvarchar(4000);

WITH CustomerIDList
AS
(
SELECT DISTINCT TOP 100
CustomerID
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
)

SELECT @cols = STUFF(
(SELECT
'],[' + CAST(CustomerID AS varchar)
FROM
CustomerIDList
ORDER BY
CustomerID
FOR XML PATH('')), 1, 2, '') + ']'

Print(@cols)


DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT OrderYear, '+
@cols +'
FROM
(SELECT CustomerID
, DATEPART(yyyy, OrderDate) as OrderYear
, TotalDue
FROM
Sales.SalesOrderHeader
) AS orgn
PIVOT
(
MAX([TotalDue])
FOR CustomerID IN
( '+
@cols +' )
) AS pvt
'

EXECUTE(@query)