Rich "The Hubbins" Claussen - Making Pivot Tables with SQL Queries

Monday, May 30, 2005

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

Developer | Tips | SQL

Monday, May 30, 2005 7:15:13 PM (Pacific Daylight Time, UTC-07:00)

Comments [0]

Comments are closed.