Published on

SQL Drill: Pivot

image
Authors
  • avatar
    Name
    David Jimenez
    Twitter

You have a SQL table that looks like this

Month Amount
Jan 30000.0000
Oct 70000.0000
Aug 60000.0000
... ...
Dec 40000.0000
May 70000.0000
Nov 30000.0000

The drill is to use that table to produce a report where each month has its own column 1:

Jan Feb Mar Apr May Jun Jul ... Dec
$50,000.00 NULL $90,000.00 NULL $230,000.00 $80,000.00 $90,000.00 ... $80,000.00

The solution is to pivot the data. The syntax to use is captured here:

pivot syntax

  • In green is the source of the data. In this case we are selecting all the rows and columns in the table MonthlyRevenue.
  • In orange is the pivot. sum determines how we are aggregating the data. The for Month in indicates that we are using Month values as the new columns.
  • In blue is how we are going to process the results from the pivot. In this case, we are formatting values as currency.

Below is the complete solution:

SELECT 
    FORMAT(Jan, 'C', 'en-us') Jan
    ,FORMAT(Feb, 'C', 'en-us') Feb
    ,FORMAT(Mar, 'C', 'en-us') Mar
    ,FORMAT(Apr, 'C', 'en-us') Apr
    ,FORMAT(May, 'C', 'en-us') May
    ,FORMAT(Jun, 'C', 'en-us') Jun
    ,FORMAT(Jul, 'C', 'en-us') Jul
    ,FORMAT(Aug, 'C', 'en-us') Aug
    ,FORMAT(Sep, 'C', 'en-us') Sep
    ,FORMAT(Oct, 'C', 'en-us') Oct
    ,FORMAT(Nov, 'C', 'en-us') Nov
    ,FORMAT(Dec, 'C', 'en-us') Dec
FROM 
(
    SELECT * FROM dbo.MonthlyRevenue
) t
PIVOT
(
    SUM(Amount)
    for Month in
    (
        [Jan]
        ,[Feb]
        ,[Mar]
        ,[Apr]
        ,[May]
        ,[Jun]
        ,[Jul]
        ,[Aug]
        ,[Sep]
        ,[Oct]
        ,[Nov]
        ,[Dec]
    )
) as PivotTable   

As a follow up, how could you accomplish the same result without using the pivot operator?

Footnotes

  1. The inspiration for this drill is the Reformat Department Table Leet Code question.