SQL Puzzle | The Pivot Puzzle – V

The requirement is you have to write a SQL statements to achieve the desired result.

For details please check out the sample input and the expected output below-

Sample Inputs

Id SoldAt Amount
1 2014-01-05 15.00
2 2015-01-05 1.50
3 2015-05-05 2.00
4 2015-05-06 4.00
5 2015-08-07 8.00
6 2015-09-25 16.00
7 2016-02-05 12.00
8 2016-04-05 6.00
9 2016-04-08 18.00
10 2016-05-07 56.00
11 2016-05-07 16.00
12 2016-07-21 1232.00

Expected Output

Year Jan Feb Mar Q01 Apr May Jun Q02 Jul Aug Sep Q03 Oct Nov Dec Q04 YTD
2014 15.00 NULL NULL 15.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15.00
2015 1.50 NULL NULL 1.50 NULL 6.00 NULL 6.00 NULL 8.00 16.00 24.00 NULL NULL NULL NULL 31.50
2016 NULL 12.00 NULL 12.00 24.00 72.00 NULL 96.00 1232.00 NULL NULL 1232.00 NULL NULL NULL NULL 1340.00

Rules/Restrictions

• The challenge is to do it with SQL statements.
• CTEs, Temp Tables are not allowed.

Script | use below script to create table & insert some sample data

--

CREATE TABLE Sale
(
Id INT
, SaleOn DATE
, Amount MONEY
)
GO

Insert Into Sale Values
(1, '20140105', \$15)
, (2, '20150105', \$1.5)
, (3, '20150505', \$2)
, (4, '20150506', \$4)
, (5, '20150807', \$8)
, (6, '20150925', \$16)
, (7, '20160205', \$12)
, (8, '20160405', \$6)
, (9, '20160408', \$18)
, (10, '20160507', \$56)
, (11, '20160507', \$16)
, (12, '20160721', \$1232);

GO

--

Solution 1

 -- /* ** SOLUTION */ SELECT * FROM ( SELECT [Year], Amount, Period FROM ( SELECT Year(SoldAt) [Year] ,Amount ,CAST(FORMAT(SoldAt,'MMM') AS VARCHAR(3)) M ,'Q'+RIGHT('0'+CONVERT(VARCHAR(2), DATEPART(q,SoldAt)), 2) Q ,'YTD' Y FROM Sale ) t1 UNPIVOT (PERIOD FOR PeriodType In (M, Q, Y)) u ) t2 PIVOT (Sum(Amount) FOR PERIOD IN (Jan, Feb, Mar, Q01, Apr, May, Jun, Q02, Jul, Aug, Sep, Q03, Oct, Nov, Dec, Q04, YTD))p --

Solution 2

 -- /* ** SOLUTION */ select [year] ,sum(case [Month] when 'January' Then amount else null end) January ,sum(case [Month] when 'February' Then amount else null end) February ,sum(case [Month] when 'March' Then amount else null end) March ,sum(case when [Month] = 'March' OR [Month] = 'February' OR [Month] = 'January' Then amount else null end) Q01 ,sum(case [Month] when 'April' Then amount else null end) April ,sum(case [Month] when 'May' Then amount else null end) May ,sum(case [Month] when 'June' Then amount else null end) June ,sum(case when [Month] = 'April' OR [Month] = 'May' OR [Month] = 'June' Then amount else null end) Q02 ,sum(case [Month] when 'July' Then amount else null end) July ,sum(case [Month] when 'August' Then amount else null end) August ,sum(case [Month] when 'September' Then amount else null end) September ,sum(case when [Month] = 'July' OR [Month] = 'August' OR [Month] = 'September' Then amount else null end) Q03 ,sum(case [Month] when 'October' Then amount else null end) October ,sum(case [Month] when 'November' Then amount else null end) November ,sum(case [Month] when 'December' Then amount else null end) December ,sum(case when [Month] = 'October' OR [Month] = 'November' OR [Month] = 'December' Then amount else null end) Q04 ,sum(amount) YTD from ( select year(soldat) [Year], DATENAME ( month , soldat ) [Month], convert(float,sum(amount)) Amount from sale group by year(soldat) , DATENAME ( month , soldat ) ) A group by [year] --

Solution 3

 -- /* ** SOLUTION */ SELECT A.[YEAR] [YEAR], CAST(JAN AS FLOAT) Jan, CAST(FEB AS FLOAT) Feb, CAST(MAR AS FLOAT) Mar, CAST(Q1 AS FLOAT) Q01, CAST(APR AS FLOAT) Apr, CAST(MAY AS FLOAT) May, CAST(JUN AS FLOAT) Jun, CAST(Q2 AS FLOAT) Q02, CAST(JUL AS FLOAT) Jul, CAST(AUG AS FLOAT) Aug, CAST(SEP AS FLOAT) Sep, CAST(Q3 AS FLOAT) Q03, CAST(OCT AS FLOAT) Oct, CAST(NOV AS FLOAT) Nov, CAST([DEC] AS FLOAT) [Dec], CAST(Q4 AS FLOAT) Q04, CAST(C.YTD AS FLOAT) YTD FROM ( SELECT [YEAR] [YEAR], JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC] FROM ( SELECT YEAR(SOLDAT) AS [YEAR], LEFT(DATENAME(MONTH,SOLDAT),3) AS [MONTH], AMOUNT FROM SALE ) AS SOURCETABLE PIVOT ( SUM(AMOUNT) FOR [MONTH] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]) ) AS PIVOTTABLE ) A INNER JOIN ( SELECT [YEAR] [Year], [1] Q1,[2] Q2,[3] Q3,[4] Q4 FROM ( SELECT YEAR(SOLDAT) AS YEAR, DATEPART(QUARTER, SOLDAT) AS QUARTERNAME, SUM(AMOUNT) AS AMOUNT FROM SALE GROUP BY DATEPART(QUARTER, SOLDAT), YEAR(SOLDAT) ) AS SOURCETABLE PIVOT ( SUM(AMOUNT) FOR QUARTERNAME IN ([1],[2],[3],[4]) ) AS PIVOTTABLE ) B ON A.YEAR = B.YEAR INNER JOIN ( SELECT SUM(AMOUNT) AS YTD,YEAR(SOLDAT) AS [YEAR] FROM SALE GROUP BY YEAR(SOLDAT) ) C ON B.YEAR = C.YEAR --

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com