Writing Lines Of Code

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 ¹:

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?

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

Footnotes