Over a million developers have joined DZone.

How to Pivot Data in SQL

· Java Zone

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

Pivoting data (Wikimedia Commons)

Most pivoting of data is done outside of relational SQL databases. Relational databases are rigid and the variable number of columns in a pivot table is not something that goes hand in hand with the rigid structures defined in relational databases. Pivot tables make some data just that much easier to work with. A good example is shown below, if you wanted to compare product sales between different months, this task would be far simpler to do if sales for each product are stored in one row. Pivoting in SQL is also useful when using a third party tool to do the pivot is overkill for your needs.

SQL CASE statements together with SQL aggregation functions are used to pivot data in a relational database. Given the following data:

Sale_DateProduct_IdUnits_Sold
2014 Jan 170119
2014 Feb 17015
2014 Mar 15701100
2013 Jan 790050
2013 Jan 190020
2014 Feb 790030
2014 Feb 1690060

The SQL statement below will pivot the detail data shown above. The result is a single row for each product_id and a column for each month with the total number of units sold per month.

SELECT
    YEAR(sale_date) AS SaleYear
     ,product_id
     , SUM(CASE WHEN MONTH(sale_date)=1 THEN units_sold ELSE 0 END) AS JanSales
     , SUM(CASE WHEN MONTH(sale_date)=2 THEN units_sold ELSE 0 END) AS FebSales
     , SUM(CASE WHEN MONTH(sale_date)=3 THEN units_sold ELSE 0 END) AS MarSales
FROM
    pivot_example
    GROUP BY
YEAR(sale_date)
,product_id


The result is shown below.

SaleYearproduct_idJanSalesFebSalesMarSales
2014701195100
201490050900

The most important aspect of using this method to pivot your data in SQL is you need to know all the possible data values in the column you would like to pivot. Another way to put it is to say need to know all the resultant pivoted column names. In the example, I pivoted the sales data into units sold per month. I know that there are only 12 months in a year and hence I know all possible columns.

It is also worthwhile to pay attention to the granularity of your data as you could end up with a very sparsely populated table. To illustrate this point have a look at what the resultant table looks like if sale_date as opposed to the SalesMonth is used to group the data.

Now imagine there where several millions of rows in the sales table.

sale_dateproduct_idJanSalesFebSalesMarSales
2014-01-017011900
2014-01-019002000
2014-01-079005000
2014-02-01701050
2014-02-079000300
2014-02-169000600
2014-03-1570100100

EDIT: 2014-02-12 SQL Server and Oracle do have built in pivot functions. Perhaps its just me but I have found the the syntax cumbersome and always feel I have to relearn the whole syntax every time I have used it. Below is a SQL statement that will work in SQL Server and give you the pivoted data.

SELECT 
            product_id 
    , [1] AS Jan_Sales 
    , [2] AS Feb_Sales 
    , [3] AS Mar_Sales
FROM
    ( 
    SELECT    
        product_id 
        ,units_sold
        ,month(sale_date) as sale_month 
    FROM pivot_example 
    ) AS source 
PIVOT(SUM(units_sold) FOR sale_month IN ( 
            [1] 
            ,[2] 
            ,[3] 
            )) AS pivot_table; 

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:
java ,sql ,tips and tricks ,tools & methods ,pivot ,relational database

Published at DZone with permission of Mpumelelo Msimanga, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}