Dynamic Pivotting + Dynamic Aggregation = Dynamic T-SQL

I’m recently approached by one of the developers asking how to swing the data listed in the rows onto the columns, but he does not know these columns ahead of time. I right away think of “Dynamic Pivoting” article written by Itzik Ben-Gan on SQL Server Magazine. So here’s the codes:

-- Populate table variable with list of order years
SET NOCOUNT ON
DECLARE @T AS TABLE(y int NOT NULL PRIMARY KEY)
INSERT INTO @T SELECT DISTINCT YEAR(orderdate) FROM dbo.Orders

-- Construct the column list for the IN clause
-- e.g., [2002],[2003],[2004]
DECLARE @cols AS nvarchar(MAX), @y AS int
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N''
WHILE @y IS NOT NULL
BEGIN
  SET @cols = @cols + N',['+CAST(@y AS nvarchar(10))+N']'
  SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))

-- Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

If you don’t care about the order of columns in @cols, then use this shorter version of codes:

-- Construct the column list for the IN clause
DECLARE @cols AS nvarchar(max);
select @cols = stuff((select distinct ',' + '[' + YEAR(orderdate) + ']' as [text()] from dbo.Orders for xml path('')), 1, 1, '')

-- Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

Here’s another challenge!

My developer then asks if he can aggregate this dynamic pivoted data as well…. So here’s what I come up with:

- Insert this dynamic pivoted data into a tempdb.dbo.someTempTable

-  Construct the statements for aggregating the pivot data

- Construct the main query using dynamic t-sql and concatenate with the statements built above

select @sql_aggr_pivot = @sql_aggr_pivot + ', SUM(pivot_tbl.[' +  rtaaa_col + '])' from @T;
SET @sql_aggr_pivot = SUBSTRING(@sql_aggr_pivot, 3, LEN(@sql_aggr_pivot));

set @sql_result = 'select .....' + @sql_aggr_pivot + ' from TableA join tempdb.dbo.someTempTable as pivot_tbl on TableA.ID=pivot_tbl.ID where this = that group by this that'

EXEC (@sql_result);

In conclusion, I guess when dealing with ‘Dynamic Pivotting’ and ‘Dynamic Aggregation’ at the same time, the only solution I can come with is to use ‘Dynamic T-SQL’