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:

• 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)