Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 1, 2005

SQL Pivot and Cross Tab

By Don Schlichting



This article will explore various SQL methods for producing pivot style reports.



Introduction


A pivot is a data mining option typically used to transform large amounts data into a condensed list. Pivot style reports display data in a grid along a vertical and horizontal axis. Financial and managerial reports are the examples usually sited. The pivot style report pictured below displays sales data; with the Year of the sales running along one axis and the Quarter the sales was made running along the other. The sales amount is the sum of all dollars for the particular year and quarter.



If a regular select were used with a group by, the result would resemble this next image. Notice the amount of rows will increase. The pivot displayed three rows, one row for each year of data. Twelve rows would be needed in the select example to display the same information. This makes the pivot style more readable, and its format usually expected in business reporting.



For Microsoft Office users, Pivot reports and tables are standard options. In Microsoft Excel, there is a drag and drop wizard that walks a user though all the steps needed to create a pivot table. The final result is the gird displayed in the first image. Unlike a simple pivot report, Excel Pivot Tables can have their Column and Rows dynamically changed by dragging the desired column to the correct axis.

In Microsoft Access, there is a wizard for creating pivot queries, called "Cross tabs." The wizard asks which columns should be on the axis, then auto-generates the code needed. Access creates pivots by using the special key word TRANSFORM:

TRANSFORM Sum(SALES.AMOUNT) AS SumOfAMOUNT
SELECT SALES.YEAR, Sum(SALES.AMOUNT) AS [Total Of AMOUNT]
FROM SALES
GROUP BY SALES.YEAR
PIVOT SALES.QUARTER;

Unfortunately, there is no wizard for producing T-SQL Pivots. In SQL 2000, pivot style reports, called "Cross-Tab Reports" can be created using the CASE keyword. In SQL 2005, CASE can be used, or there is a new keyword, PIVOT.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM