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’
