# 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

Monday, May 30, 2005 7:15:13 PM (Pacific Daylight Time, UTC-07:00)  #    Comments [0]
Comments are closed.