Some time ago, I was asked to devlop some reports for a little tracking application I'd written for my employer. In my past life, I did a lot of this work (heck, it was my job!) and feel pretty comfortable using Excel to twist my numbers around in all sorts of Pivot-y ways.
What I hadn't done is to create pivots that were conditional and customized. I suppose, with further exploration, I'd find reasonable ways to accomodate my needs from within Excel, but I had a moment's epiphany - why not just build the proper pivot query in SQL to begin with?
I know I had seen it done, but never took the time to find out details. So, with this new thought, I did a bit of exploring - Duh! That's easy! Simply count conditionally for each column.
So, for posterity's sake - and my own future needs - here follows the basic structure for doing a pivot table with a SQL query (Counts number of items for a given number of weeks prior to current):
SELECT TOP 100 PERCENT
'Opened On Week' AS LineType,
'All Modules' AS AppModule,
COUNT( CASE WeeksBackOpened WHEN 4 THEN IssueId ELSE NULL END )
AS "4 Weeks Ago",
COUNT( CASE WeeksBackOpened WHEN 3 THEN IssueId ELSE NULL END )
AS "3 Weeks Ago",
COUNT( CASE WeeksBackOpened WHEN 2 THEN IssueId ELSE NULL END )
AS "2 Weeks Ago",
COUNT( CASE WeeksBackOpened WHEN 1 THEN IssueId ELSE NULL END )
AS "Last Week",
COUNT( CASE WeeksBackOpened WHEN 0 THEN IssueId ELSE NULL END )
AS "This Week"
FROM PivotTableView
UNION
SELECT TOP 100 PERCENT
'Opened On Week' AS LineType,
Application + ': ' + ModuleName AS AppModule,
COUNT( CASE WeeksBackOpened WHEN 4 THEN IssueId ELSE NULL END )
AS "4 Weeks Ago",
COUNT( CASE WeeksBackOpened WHEN 3 THEN IssueId ELSE NULL END )
AS "3 Weeks Ago",
COUNT( CASE WeeksBackOpened WHEN 2 THEN IssueId ELSE NULL END )
AS "2 Weeks Ago",
COUNT( CASE WeeksBackOpened WHEN 1 THEN IssueId ELSE NULL END )
AS "Last Week",
COUNT( CASE WeeksBackOpened WHEN 0 THEN IssueId ELSE NULL END )
AS "This Week"
FROM PivotTableView
GROUP BY Application, ModuleName
ORDER BY AppModule, LineType