What is a Pivot Table in SQL?

by AJ Graham
What is a Pivot Table in SQL?

What is a Pivot Table in SQL?

In SQL, a pivot table is a set of data that is transformed from a collection of separate rows to a collection of columns. In relational databases, such as Microsoft SQL Server, Oracle and MySQL, pivot tables can be used to simplify extensive data in order to make it easier to read and understand. To create a pivot table, an aggregate is used against a set of data to distribute multiple rows of a single column into a single row with multiple columns. This essentially pivots the result set sideways.

Sample Data

To better understand a pivot table, an example of some sales data is listed here. Copy the following into Microsoft SQL Server Management Studio to try out the examples. Create table #PivotTestTable (CustName varchar(8), Item_Type varchar(8), Item_Amount numeric(6,2)) insert into #PivotTestTable select 'Jason', 'Computer', 435.34 union select 'Jason', 'Software', 243.54 union select 'Jason', 'Monitor', 158.23 union select 'Alison', 'Computer', 345.89 union select 'Alison', 'Software', 78.78 union select 'Alison', 'Monitor', 123.45

Starting UnPivoted Data

When the temp table, #PivotTestTable, is queried, the result is the following. CustName Item_Type Item_Amount -------- --------- ----------- Alison Computer 345.89 Alison Monitor 123.45 Alison Software 78.78 Jason Computer 435.34 Jason Monitor 158.23 Jason Software 243.54 As you can see, the result set shows two customers, Alison and Jason, who have purchased three different types of items. There are six rows of data for two customers. If we wanted to see the data in a single row per customer, we would use a pivot table to achieve the desired result.

Pivot by PIVOT function

Microsoft SQL Server has a PIVOT function built into SQL Server. Here is an example with the #PivotTestTable data. SELECT CustName as Total_Sales_By_Cust, Computer, Monitor, Software FROM ( SELECT CustName, Item_Type, Item_Amount FROM #PivotTestTable ) a PIVOT ( sum(Item_Amount) FOR Item_Type in (Computer, Monitor,Software) ) b This query will return the original six rows pivoted into two rows with separate columns for each type of item sold. The result set generated from this query is here: Total_Sales_By_Cust Computer Monitor Software ------------------- --------- -------- --------- Alison 345.89 123.45 78.78 Jason 435.34 158.23 243.54

Pivot by Aggregated Case Statement

By using an aggregate function (SUM, AVG, MIN, MAX) around a case statement in a SQL query, we are able to achieve the same result as the PIVOT function with less work. SELECT CustName as Total_Sales_By_Cust, sum(case Item_Type when 'Computer' then Item_Amount end) as Computer, sum(case Item_Type when 'Monitor' then Item_Amount end) as Monitor, sum(case Item_Type when 'Software' then Item_Amount end) as Software FROM #PivotTestTable GROUP BY CustName This query will return the exact same result set of the previous example and is only a preference for which type of pivot to use.

Common Mistake with Pivot Tables

A common mistake to create a pivot table is to create a join back on the source table. This will produce unreliable results and should be avoided. This example is strictly an example of what not to do. The result in this sample will be the same; however this sample will not work in all cases. SELECT p1.CustName, p1.Item_Amount as Computer, p2.Item_Amount as Monitor, p3.Item_Amount as Software FROM #PivotTestTable p1 INNER JOIN #PivotTestTable p2 on p1.CustName = p2.CustName and p2.Item_Type = 'Monitor' INNER JOIN #PivotTestTable p3 on p1.CustName = p3.CustName and p3.Item_Type = 'Software' WHERE p1.Item_Type = 'Computer'

References

About the Author

AJ Graham is a Business Intelligence and Data Warehousing specialist focusing on Microsoft SQL Server. He has written technical articles for sites such as DemandStudios.com and eHow.com. AJ graduated from the University of South Florida with a degree in Management Information Systems.