Cross-tab Queries with Transact-SQL

How to use sp_transform

The sample procedure sp_transform dynamically generates a Transact SQL Query that returns a resultset similar to the Cross-Tab Queries in Access or Excel.

Here is a short example (using the database Northwind), that shows how to use this stored procedure

Prerequisites:

Before you can run this example create the stored procedure sp_transform either in your master-database or locally in your Northwind database. This demo will run with Microsoft SQL Server Version 7.0 und Version 2000

Examples

The examples use a crosstab-query to show the total and average amount of freight costs per year by Employee.

In the first step we create a view, that combines the information from the orders and the employees table using an INNER JOIN.

The result of this view will look similar to the following list:

The Cross-Tab Query (using sp_transform)

Using this view as a starting point, we will now generate a resultset that shows the Total freight cost per employee per year.

First example

In the first example we will use lastname as the row-header and theYear as the column-header, freight is the value field and 'SUM' the aggregate function:

The result:

The second example just shows what the result looks like when you swap the row-header and column-header fields.

The result:

The next example shows the same Query using the AVG-function

The result: