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