What is a Pivot Table in SQL

Author: AJ Graham

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.

Here is another example

and another

What is a Pivot Table in SQL?thumbnail 

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 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’

Ads by Google

References

Read more: What is a Pivot Table in SQL? | eHow.com http://www.ehow.com/about_5336679_pivot-table-sql.html#ixzz1Lbg55I7L

Advertisements