- Published on
SQL Drill: Pivot
- Authors
- Name
- David Jimenez
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:
- 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. Thefor 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
-
The inspiration for this drill is the Reformat Department Table Leet Code question. ↩