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.
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
insert into #PivotTestTable
select 'Jason', 'Computer', 435.34
select 'Jason', 'Software', 243.54
select 'Jason', 'Monitor', 158.23
select 'Alison', 'Computer', 345.89
select 'Alison', 'Software', 78.78
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.
CustName as Total_Sales_By_Cust,
FOR Item_Type in (Computer, Monitor,Software)
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.
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
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.
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'
How to Create Pivot Tables With MS Access Samples
Pivot tables can reveal customer geographical information instantly. They can be used to sum up orders by state, city and company and...
How to Use the Pivot Table Wizard in Excel
Use the pivot table wizard in Excel and select what ranges should be included in a pivot table. Discover how to use...