All categories for each week and group

Hi I have a data source as such

Data:

Weeknum	Group	Priority	Total
202106	A	High	10
202106	B	Medium	15
202107	A	Medium	88

Priorities:

Priority
High
Medium
Low

The data has already been grouped such that each weeknum+group+priority is unique. What I need to do is return the full list of priorities for each week/group including zeros:

Weeknum	Group	Priority	Total
202106	A	High	10
202106	A	Medium	0
202106	A	Low	0
202106	B	High	0
202106	B	Medium	15
202106	B	Low	0
202107	A	High	0
202107	A	Medium	88
202107	A	Low	0

It doesn’t matter if it also returns records for 202107/B as well but it must return a record for each priority.

Any help appreciated. :

Answer

Use a cross join to generate the rows and then left join to bring in the columns you want:

select wg.weeknum, wg.group, p.priority,
       coalesce(t.total, 0) as total
from (select distinct weeknum, group
      from t
     ) wg cross join
     priorities p left join
     t
     on t.weeknum = wg.weeknum and t.group = wg.group and
        t.priority = p.priority;