HOW TO: PIVOT in SQL Server 2005 to produce cross tabulation

There was no easy way to produce a cross tabulation in SQL Server 2000 but in SQL Server 2005, this is achievable using the PIVOT command. Here is a example on how to produce a cross tabulation in SQL Server 2005.

In the example below, the first table, named EventResult contains the results for an athletic competition. The second table is what we will produce and it’s the cross tabulation of the EventResult table.

Cross tabulation in SQL Server 2005

The T-SQL example below shows you how to produce the cross tabulation mentioned earlier using the PIVOT command.

T-SQL example using PIVOT command to produce cross tabulation

Explanation: Let’s start with the sub-query within the T-SQL query.

What is being PIVOT are the values of the Event column, taking each value and making them into columns ([100m], [200m], [800m]) and each pivoted column will contain SUM(Time).

Sub-Query showing PIVOT in T-SQL

The SELECT command highlighted in grey shows the result of the pivoted table as Name, [100m], [200m], [800m].

SELECT command showing the PIVOT column headers

It may look a little confusing at first glance but I suggest that you create the example in SQL Server 2005 and try out the example and you will see the relationship between the EventResult table, the PIVOT table (cross tabulation) and the T-SQL.