SQL Pivot and CrossTabs

Occasionally a situation arises where normalized data just won’t do, consider for example, for statistical usage.

Pivot is built in SQL command (like select, etc) and it takes data and makes a table out of it. That is, given pairs, it can construct a matrix for you.

A CrossTab is a built in MS Access command that does pivot. CrossTab is not built into SQL, you have to create your own script. Better yet, find and utilize one of many SQL CrossTab scripts existing on the internet, ranging from simple to sophisticated, sometimes using the SQL command pivot and sometimes not (in case you cannot use the pivot command on your server).

Suppose we have data:

dbo.[ChessPieceLayout]
ID RowID ColID PieceID
43 1         A      3
44 2         B      7
45 7         A      1
46 5         E      4
SELECT * FROM
( SELECT DISTINCT ID, RowID, ColID, PieceID FROM ChessPieceLayout ) as L
PIVOT ( COUNT(PieceID) for ColID in ( [A], [B], [C], [D], [E], [F], [G], [H] ) ) p
) as pivotedtable
ROW A    B    C    D    E    F    G    H
1   1    0    0    0    0    0    0    0
2   0    0    0    0    1    0    0    0
5   0    1    0    0    0    0    0    0
7   1    0    0    0    0    0    0    0

And we get a 1/0 indicator chart of square occupancy. You can see we are missing some rows, inner join our table L to the full set of rows to fix that. This is just a basic example of what we can do.

Pivot Table Syntax

SELECT * FROM
(select ... ) ALIAS1
PIVOT (AGG_FN(ID1) for ID2 in (ID2_0, ID2_1, ... ID2_N) ) ALIAS2
) as PIVOTED_TABLE

ALIAS1 and ALIAS2 are essential for the pivot tables, you MUST rename your tables for the pivot statement. Note that ID1 and ID2 do not have to be the same. ID1 is the thing you want to aggregate, and ID2 is the column names. If the value of ID2 is not contained in the list of columns, then it will not appear in your output. (ie, if we had a datarecord inside table above (48, 1, Z, 5), it would not show up in our table because X is not contained in the column list.)

CrossTab vs Pivot

Some things to keep in mind. If you use a CrossTab, you will either be writing your own complicated script or diving into another coders. Trying to comprehend a piece of code that I didn’t write is not quite my cup of tea, so I prefer to use pivot statements and write my own sql query from scratch.

Pivot Nested Aggregate Functions

Pivot statements require exactly one aggregate function – avg, sum, count, max, or min. You can’t nest these aggregate functions, so set up your pivot source table accordingly.

Pivot Dynamic Column Names

Lastly, Pivot tables require you to define all of your desired columns – by name. The one workaround to this is to make your query into a string and execute it with the ‘exec’ command or sp_executesql. You can create your column names using some kind of loop and string logic.
There are three ways to form this column name string: type it all out by hand, use a lookup table, or use the system tables to obtain your column names. The prettiest is the lookup table.

	DECLARE @list varchar(MAX)
	SET @list= ''
	SELECT
		@list = @list + '[' + Cast(ColumnID As varchar(5)) + '],'
	FROM lookupTable
	SET @list = SUBSTRING(@list, 1, Len(@list) - 1) --trim trailing comma

Incorrect syntax near ‘PIVOT’

This is caused by the database compatibility level being lower than what’s needed. Level 80 is SQL 2000, Level 90 is SQL 2005. Pivot needs SQL 2005, so :

EXEC sp_dbcmptlevel 'mydb', 90

PIVOT is a very neat little operation that can be used to denormalize data, used by statistics software for example. More : http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx