Most of the times we want to create a result in which the columns are dynamic (for example, they are read from a table), then we need to construct the PIVOT query dynamically. Here is an example which uses the dynamic PIVOT functionality.
CREATE TABLE dbo.sales( SalesPerson VARCHAR(100) , STATE CHAR(2) , Amount INT )
INSERT INTO dbo.sales VALUES ( 'John', 'MD', 120 ), ( 'John', 'VA', 500 ), ( 'John', 'NY', 900 ), ( 'Jane', 'MD', 40 ), ( 'Jane', 'VA', 125 )
DECLARE @cols AS NVARCHAR(MAX) , @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.State)
FROM dbo.sales c
FOR XML PATH(''),TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'SELECT SalesPerson, ' + @cols + ' from
(
SELECT SalesPerson
, STATE
, AMOUNT
FROM dbo.sales
) x
PIVOT
(
MAX(amount)
FOR state IN ('
+ @cols + ')
) p '
EXECUTE(@query) DROP TABLE dbo.sales
