Saturday, February 14, 2009

Pivoting your data in MSSQL Creating "Cross Tab" reports

A pivot is a normally used to transform large amounts data into a condensed list or grid. A Pivot report displays data in a grid along a vertical and horizontal axis. Financial reports are good candidates for Pivot reports.

Microsoft Access refers to pivots as "Cross tabs." In MS Acces there is a wizard as well. It asks which columns should be on which axis, then auto-generates the code needed. Access creates pivots by using the special key word TRANSFORM.

Unfortunately it is not as easy as using a wizard in MS SQL. In SQL 2000, pivot style reports, called "Cross-Tab Reports" can be created using the CASE keyword. CASE can also be used in SQL 2005 and SQL 2008. But there is another keyword that can be used, PIVOT.

Read More..