In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database.  Consider the result of a crosstab operation -- the columns returned will vary depending on the data.  There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes.  Also, in T-SQL there is no easy way to dynamically pivot data, and even doing it with hard-coded pivot values results in long, complicated SELECT statements.  So, in my opinion there is really is no benefit of doing this transformation within SQL Server.  (Unless you are in the mood for some fun dynamic sql and cursors ...)

Almost all modern report designers allow you to quickly and easily crosstab a standard recordset from SQL Server, so be sure to learn how to use this feature and take advantage of it.  Some are more flexible than others, but for the most part they work as you'd expect.  Either way, for report-writing crosstabing data at the presentation layer should not be a concern.

The main issue is programmers who are writing their own presentation layer.  Many programmers have difficulty writing code to do the transformation; I believe this may be why it is common to be tempted to force SQL Server to do all of the work.

Here's a quick example of an easy way to implement a crosstab at the presentation layer using C#. To keep things short and simple, I have just written a small static function that accepts any object that implements IDataReader and returns a crosstabbed DataTable object.  The code for the function is here.

All you need to do is open up an IDataReader object that returns the data you wish to Pivot.  The data should be fully summarized by SQL Server already -- i.e., you should GROUP BY your pivot column plus any other columns you wish to return, and you should aggregate your pivot value accordingly.  The DataReader should also be sorted so that all rows which will be pivoted into one are sorted together.  

For example, consider the Orders table in the Northwind database.  Suppose you want to display CompanyNames as rows, ProductNames as columns, and the total quantity of orders per CompanyName per ProductName in your crosstab.  The DataReader's recordset should be grouped by CompanyName and ProductName, it should include a SUM(Qty) calculation, and it should be sorted by CompanyName.  (see the example for this exact scenario using Northwind). 

What's nice is, this is all standard SQL and requires very little effort in terms of writing the SELECT and for the server to process and return the results.  We are letting the presentation layer handle the formatting.  Another benefit is that we can use this technique with any datasource that ADO.NET can connect to, not just SQL Server.  (In fact, we can do this with anything that implements IDataReader)

The arguments for the Pivot() function are as follows:

public static DataTable Pivot (IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)

• dataValues -- this is any open DataReader object, ready to be transformed and pivoted into a DataTable.  As mentioned, it should be fully grouped, aggregated, sorted and ready to go. 
• keyColumn -- This is the column in the DataReader which serves to identify each row.  In the previous example, this would be CustomerID.  Your DataReader's recordset should be grouped and sorted by this column as well.
• pivotNameColumn -- This is the column in the DataReader that contains the values you'd like to transform from rows into columns.   In the example, this would be ProductName.
• pivotValueColumn -- This is the column that in the DataReader that contains the values to pivot into the appropriate columns.  For our example, it would be Qty, which has been defined in the SELECT statement as SUM(Qty).

And, that's it.  You just call the Pivot() function on any object that implements IDataReader, specify the pivot information, and a DataTable is returned that you can use however you like.  The example uses this to fill up a DataGrid so you can see the results on a form.

Stay tuned for a performance comparison between this method and doing the crosstab transformation on the SQL Server .... You might be surprised by the results.

• Keep those SQL Crosstabs flexible!
• .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)