I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as:
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
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:
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
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 !!)
- Keep those SQL Crosstabs flexible!
- .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable
- .NET CrossTabs versus SQL Server CrossTabs
- ASP and ADO Pivots – Old School CrossTabs
- Sorting Columns with the C# Pivot Function
- SQL 2005 PIVOT Operator (link)
|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