Crosstab Query (Rows to Columns)

First,  columns to rows are very simple with UNION:

SELECT col1 as [new column]  FROM table1
UNION
SELECT col2 FROM table1
UNION
SELECT col3 FROM table1

This is very helpful when I need to run aggregate function on multiple columns:

SELECT MAX([new column]) as [Max Date] FROM
(SELECT col1 as [new column]  FROM table1
UNION
SELECT col2 FROM table1
UNION
SELECT col3 FROM table1) as temp

Rows to columns are a little tricky, depends on the requirement:

A few examples:

• A report to run aggregate function on selected rows in a table

SELECT MAX(product_id) as ID, /* for joining purpose*/
SUM( CASE WHEN category = ‘drink’  THEN Sales ELSE null END ) as [Drink Total],
SUM( CASE WHEN category = ‘food’  THEN Sales ELSE null END ) as [Food Total]
FROM sales_table

• A report to list repeating info from a table in horizontal format

Step 1: get a table with unique identifier for each repeating row:

SELECT product_id,  sales_date,  ROW_NUMBER() OVER (ORDER BY sales_date) as sort
FROM sales_table
WHERE product_id = 222 /* this is very important. otherwise ROW_NUMBER() will not have correct value */

Step 2: getting each one by its unique # into horizontal rows, by using aggregate functions:

SELECT MAX(product_id) as ID, /* has to use a aggregate function to get one value back */
MAX( CASE sort WHEN 1 THEN sales_date ELSE null END ) as [Sales Date 1],
MAX( CASE sort WHEN 2 THEN sales_date ELSE null END ) as [Sales Date 2],
MAX( CASE sort WHEN 3 THEN sales_date ELSE null END ) as [Sales Date 3]
FROM
(SELECT product_id,  sales_date,  ROW_NUMBER() OVER (ORDER BY sales_date) as sort
FROM sales_table WHERE product_id = 222) as temp /* use above table in subquery */

Step 3: since we have to take the product ID in the subquery, we need to convert above query into a function:

CREATE FUNCTION dbo.GetSalesDate( @product_id int, @index int )
RETURNS DATETIME
AS
BEGIN
DECLARE @ret DATETIME
SELECT @ret = t.sales_date
FROM
(SELECT sales_date, ROW_NUMBER() OVER (ORDER BY sales_date) as sort FROM sales_table WHERE product_id=@product_id) as t
WHERE sort = @index
RETURN @ret
END
GO

Then you can run:

SELECT p.*, dbo.GetSalesDate( product_id, 1), dbo.GetSalesDate( product_id, 2), dbo.GetSalesDate( product_id, 3)
FROM product_table

Unfortunately this is a very expensive function call.

The above query still hard coded the number of columns. Here is a post on dynamic crosstab.

Advertisement