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.

Update( FIELD_NAME) in SQL trigger

In SQL trigger programming, sometimes people put

if ( update( FIELD_NAME ) )
BEGIN
END

to determine if a field is being changed or not. However the SQL server doesn’t seems to really check the previous and new value. As long as the field is being updated in the UPDATE statement, this update() function will return true.

So if you want to record the real value change, you have to compare the value from deleted and inserted table yourself.