Dynamic PIVOT

-- Dynamic PIVOT

DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

DECLARE

@cols AS NVARCHAR(MAX),

@y    AS INT,

@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause

-- e.g., [2002],[2003],[2004]

SET @cols = STUFF(

(SELECT N',' + QUOTENAME(y) AS [text()]

FROM (SELECT DISTINCT cast([DATE] as Date) AS y FROM dbo.BuildReportByMonth) AS Y

ORDER BY y

FOR XML PATH('')),

1, 1, N'');

-- Construct the full T-SQL statement

-- and execute dynamically

SET @sql = N'SELECT *

FROM (SELECT DecimalId, HexId, Date, isnull(HttpBw, 0) + isnull(HttpsBw, 0) as Usage

FROM dbo.BuildReportByMonth b

join DecHex d on b.CustomerId = DecimalId) AS q

PIVOT(SUM(Usage) FOR Date IN(' + @cols + N')) AS P

order by DecimalId;';

EXEC sp_executesql @sql;

GO