Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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
FROM
  SomeTable
GROUP BY CustomerID

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
FROM
  SomeTable
GROUP BY CustomerID

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:

Print | posted on Friday, October 24, 2003 4:40 PM | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques ]

Feedback

Gravatar

# 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
9/28/2005 5:05 AM | Cedric
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET