Following T-SQL sample shows how to use dynamic query in a pivot operator:

Normal query:

SELECT Name, Max(TaxRate) TaxRate from Sales.SalesTaxRate GROUP BY Name

Pivot query:

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + + ',[' + Name + ']', '[' + Name+ ']') FROM Sales.SalesTaxRate GROUP BY Name

EXEC (’SELECT * FROM (SELECT NAME, TaxRate FROM Sales.SalesTaxRate) AS A PIVOT( MAX(TaxRate) FOR Name IN (‘ + @PivotColumnHeaders + ‘)) AS B’)

HTH