Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Keep those SQL Crosstabs flexible!

I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as:

Select CustomerID,
  SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land,
  SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea,
  SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air

This made the person I helped happy – it worked well and gave him what he wanted. 

Of course, I had to ask: “These 3 values – Land, Sea, Air – can they ever change?”  And he indicated that they might – they are user definable.  There can only be 3, but the user can name them as they like. 

The solution, then, was to add a column called “ColIndex” to the master table of “Types” with values of 1-3 for each type and to re-write the query this way:

Select CustomerID,
  SUM(CASE WHEN ColIndex=1 THEN Amount ELSE 0 END) as Type1,
  SUM(CASE WHEN ColIndex=2 THEN Amount ELSE 0 END) as Type2,
  SUM(CASE WHEN ColIndex=3 THEN Amount ELSE 0 END) as Type3

This allowed the cross tab to work regardless of their names.  The other advantage is now the columns returned by this query are constant – they never change regardless of the data in the database.  Just generic Type1, Type2, etc are returned.  There is a limit to the number of Types we can handle – that must be fixed without using dynamic SQL – but now we can handle changed Types without re-writing any code.

The final thing to do, then, at the presentation layer just make sure to display column headers containing the names for each type.  Much better to simply present changing column headers than to actually physcially change the column names in your views or stored procs.

The moral:  keep things generic, plan for the future, and let the presentation layer do its share of the work. 

(of course, if the presentation layer is capable of pivoting – even better !!)

see also:

Legacy Comments

re: Keep those Cross-tabs flexible!
Do u have a solution for varaible number of columns? not just 3, that depends on the rows? Thanks