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


    FROM #PivotTestTable
    ) a


    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.


    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.


    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'


Promoted By Zergnet


You May Also Like

Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!