Listing 4: Using Dynamic SQL to Construct a PIVOT Query

USE Northwind;

DECLARE @sql AS NVARCHAR(1000);

SET @sql = N'SELECT *
FROM (SELECT EmployeeID, ShipVia, Freight
  FROM dbo.Orders) AS D
  PIVOT(SUM(Freight)
        FOR ShipVia IN(' +
STUFF(
  (SELECT N',' + QUOTENAME(ShipperID) AS [text()]
   FROM dbo.Shippers
   ORDER BY ShipperID
   FOR XML PATH('')), 1, 1, N'') +
N')) AS P;';

EXEC sp_executesql @sql;