Full join with MS SQL Server

Question

I wanted to use full join to solve this leetcode question: https://leetcode.com/problems/reformat-department-table/

The issue is that since not all months are in all department IDs, so I'm getting a Null row which should be targeted for department 3. Any chance this type of question can be solved by doing full joins?

/* Write your T-SQL query statement below */

select m1.id, 
m1.revenue as Jan_Revenue,
m2.revenue as Feb_Revenue,
m3.revenue as Mar_Revenue,
m4.revenue as Apr_Revenue,
m5.revenue as May_Revenue,
m6.revenue as Jun_Revenue,
m7.revenue as Jul_Revenue,
m8.revenue as Aug_Revenue,
m9.revenue as Sep_Revenue,
m10.revenue as Oct_Revenue,
m11.revenue as Nov_Revenue,
m12.revenue as Dec_Revenue
from (select * from Department where month = 'Jan') m1 
full join (select * from Department where month = 'Feb') m2
on m1.id = m2.id
full join (select * from Department where month = 'Mar') m3
on m2.id = m3.id
full join (select * from Department where month = 'Apr') m4
on m3.id = m4.id
full join (select * from Department where month = 'May') m5
on m4.id = m5.id
full join (select * from Department where month = 'Jun') m6
on m5.id = m6.id
full join (select * from Department where month = 'Jul') m7
on m6.id = m7.id
full join (select * from Department where month = 'Aug') m8
on m7.id = m8.id
full join (select * from Department where month = 'Sep') m9
on m8.id = m9.id
full join (select * from Department where month = 'Oct') m10
on m9.id = m10.id
full join (select * from Department where month = 'Nov') m11
on m10.id = m11.id
full join (select * from Department where month = 'Dec') m12
on m11.id = m12.id

Using `FULL JOIN` is not a great option here, but if you want to use it, you probably need to change the first field `m1.id`. If m1 didn't have a row for that month, the ID would be NULL. Instead, it needs to be any ID e.g., `COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id, m8.id, m9.id, m10.id, m11.id, m12.id)` - noting that COALESCE finds the first non-NULL record in the list. — seanb, Dec 07 '21 at 06:16

score 0 · Answer 1 · answered Dec 07 '21 at 05:13

Instead of doing 12 passes of the Department table, just do 1 pass with 12 case statements:

Select id, sum(Case When month='Jan' Then revenue Else 0 End) as JanRevenue
      , sum(Case When month='Feb' Then revenue Else 0 End) as FebRevenue
      , sum(Case When month='Mar' Then revenue Else 0 End) as MarRevenue
      , sum(Case When month='Apr' Then revenue Else 0 End) as AprRevenue
      , sum(Case When month='May' Then revenue Else 0 End) as MayRevenue
      , sum(Case When month='Jun' Then revenue Else 0 End) as JunRevenue
      , sum(Case When month='Jul' Then revenue Else 0 End) as JulRevenue
      , sum(Case When month='Aug' Then revenue Else 0 End) as AugRevenue
      , sum(Case When month='Sep' Then revenue Else 0 End) as SepRevenue
      , sum(Case When month='Oct' Then revenue Else 0 End) as OctRevenue
      , sum(Case When month='Nov' Then revenue Else 0 End) as NovRevenue
      , sum(Case When month='Dec' Then revenue Else 0 End) as DecRevenue
From Department
Group by id

Full join with MS SQL Server

1 Answers1