-- 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
