One of the cool new commands introduced in SQL 2005 was the PIVOT command (and UNPIVOT). One thing that has always irked me about PIVOT was that you need to “hard code” the column names you want to actually pivot on. This is usually fine for a static report, say you want to pivot on Apples, Oranges, Bananas. Great. But what if you want to pivot on Month/Year? “Apr 2009”, “May 2009”, etc, etc. Still ok, if you want to hardcode your month years and they never change. But in some cases, you need to keep adding the current “Month Year” combo to the query.
You can go the old fashioned route and create a dynamic SQL statement and do an EXEC (@dynamicSql) , or you can try to figure out a way to do your PIVOT dynamically.
What I did here is create a dataset and added a numeric ordering and then pivoted on that instead of the the actual value “Month Name Year” (eg: “May 2009”). That way, my pivot is always the same, 6 months worth, or whatever you like, and you can pass in parameters for the date filters in the top query to get your correct range.
-- get your distinct month/year vals into a temp table -- need temp table here because CTE doesnt like a subquery and row_number SELECT DISTINCT CalendarMonthYearName, MonthOfYear,CalendarYear INTO #tmp_monthyears FROM dbo.DimDate WHERE Date > '02/01/2009' AND Date < '07/01/2009' ORDER BY CalendarYear DESC,MonthOfYear DESC; -- create a CTE that adds a "Row Number" which is number 1-6 WITH CalMonthYears AS ( -- add your number SELECT CalendarMonthYearName AS 'CalendarMonthYearName', MonthOfYear, CalendarYear, ROW_NUMBER() OVER (ORDER BY CalendarYear DESC) AS 'YearMonthNum' FROM #tmp_monthyears ) SELECT ItemNumber, ,,,,, FROM ( -- your query to get your data, pivot this data SELECT YearMonthNum,s.ItemNumber, SUM(Quantity) AS 'Quantity' FROM DataHistory s INNER JOIN dbo.DimDate d ON s.DateKey = d.DateKey INNER JOIN CalMonthYears cmy ON d.CalendarMonthYear = cmy.CalendarMonthYearName GROUP BY YearMonthNum,s.ItemNumber ) AS SourceTable PIVOT ( SUM(Quantity) FOR YearMonthNum IN ( ,,,,, ) ) AS PivotTable; DROP TABLE #tmp_monthyears
The sky is the limit with this, you can modify this or think of other ways to use this logic to make your PIVOT commands dynamic, so you don’t have to keep editing stored procedures every time your pivot columns change 🙂
One reply on “Using CTE’s to Create Dynamic Pivot Tables in SQL 2005/2008”
You can combine this with dynamic code to re-alias the ,, columns through a lookup table/CTE to get back to Monday, Tuesday, Wednesday or January, February, March as well.