SQL Puzzle | The Pivot Puzzle – V [ 3 Solutions :) ]

Tags

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.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

2 thoughts on “SQL Puzzle | The Pivot Puzzle – V [ 3 Solutions :) ]”

Pingback: SQL Puzzle | Dynamic Pivot Puzzle | Improving my SQL BI Skills
revank219 said:

June 27, 2021 at 8:45 PM

;with cte as (
select Amount,Year(SaleOn)Years,left(DATENAME(month,SaleOn),3)months
from Sale
),cte1 as
(select Years,”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”oct”,”Nov”,”Dec” from cte
pivot (
sum(Amount)for Months in (“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”oct”,”Nov”,”Dec”)
)as p),cte2 as (select Years,Max(Jan)Jan,Max(Feb)Feb,Max(Mar)Mar,
Max(Apr)Apr,Max(May)May,Max(Jun)Jun,Max(Jul)Jul,Max(Aug)Aug,Max(Sep)Sep,Max(oct)oct,Max(Nov)Nov,Max(Dec)Dec
from cte1
group by Years)
,cte3 as (
select Year(SaleOn)Years,Amount,concat(‘Q’,DATEPART(QUARTER, Saleon)) AS QUARTERNAME from sale
),
cte4 as (
select Years,”Q1″,”Q2″,”Q3″,”Q4″ from cte3
pivot (
sum(Amount) for QUARTERNAME in (“Q1″,”Q2″,”Q3″,”Q4”)
)as piv
)
,cte5 as (
select year(saleon)years,sum(Amount)YTD from sale group by year(saleon)
)
select cte2.Years,Jan,Feb,Mar,Q1,Apr,May,Jun,Q2,Jul,Aug,Sep,Q3,oct,Nov,Dec,Q4,YTD
from cte2 inner join cte4
on cte2.years = cte4.years
inner join cte5 on cte2.years = cte5.years

LikeLike

Share this

Related

2 thoughts on “SQL Puzzle | The Pivot Puzzle – V [ 3 Solutions :) ]”

Leave a comment Cancel reply