How to expand categories by id in SQL

I have the following table:

org_id | person_id | month      | consultation_mode | professional_domain | number_consultations
1      | 1         | 01-03-2018 | in person         | doctor              | 3
1      | 1         | 01-03-2018 | telephone         | doctor              | 2
1      | 1         | 01-03-2018 | telephone         | nurse               | 1
2      | 4         | 01-01-2015 | telephone         | doctor              | 2
2      | 4         | 01-01-2015 | home visit        | other               | 4
2      | 4         | 01-12-2021 | null              | null                | null

I would like to have for each org_id, person_id, month a line for each category of consultation_mode and professional_domain, as per the below:

org_id | person_id | month      | consultation_mode | professional_domain | number_consultations
1      | 1         | 01-03-2018 | in person         | doctor              | 3
1      | 1         | 01-03-2018 | in person         | nurse               | 0
1      | 1         | 01-03-2018 | in person         | other               | 0
1      | 1         | 01-03-2018 | telephone         | doctor              | 2
1      | 1         | 01-03-2018 | telephone         | nurse               | 1
1      | 1         | 01-03-2018 | telephone         | other               | 0
1      | 1         | 01-03-2018 | home visit        | doctor              | 0
1      | 1         | 01-03-2018 | home visit        | nurse               | 0
1      | 1         | 01-03-2018 | home visit        | other               | 0
2      | 4         | 01-01-2015 | in person         | doctor              | 0
2      | 4         | 01-01-2015 | in person         | nurse               | 0
2      | 4         | 01-01-2015 | in person         | other               | 0
2      | 4         | 01-01-2015 | telephone         | doctor              | 2
2      | 4         | 01-01-2015 | telephone         | nurse               | 0
2      | 4         | 01-01-2015 | telephone         | other               | 0
2      | 4         | 01-01-2015 | home visit        | doctor              | 0
2      | 4         | 01-01-2015 | home visit        | nurse               | 0
2      | 4         | 01-01-2015 | home visit        | other               | 4
2      | 4         | 01-12-2021 | in person         | doctor              | 0
2      | 4         | 01-12-2021 | in person         | nurse               | 0
2      | 4         | 01-12-2021 | in person         | other               | 0
2      | 4         | 01-12-2021 | telephone         | doctor              | 0
2      | 4         | 01-12-2021 | telephone         | nurse               | 0
2      | 4         | 01-12-2021 | telephone         | other               | 0
2      | 4         | 01-12-2021 | home visit        | doctor              | 0
2      | 4         | 01-12-2021 | home visit        | nurse               | 0
2      | 4         | 01-12-2021 | home visit        | other               | 0

There are 3 consultation_mode: in person, telephone, home visit & 3 professional_domain: doctor, nurse, other. How can I do this in SQL?

Answer

First, you will need a small table or CTE containing the full set of consultation_mode + professional_domain combinations, then you will use a CROSS JOIN (i.e. a Cartesian product) between this table and the unique set of (org_id, person_id, month), then you will LEFT JOIN your starting table to that.

Something like this:

table raw_data:

org_id | person_id | month      | consultation_mode | professional_domain | number_consultations
1      | 1         | 01-03-2018 | in person         | doctor              | 3
1      | 1         | 01-03-2018 | telephone         | doctor              | 2
1      | 1         | 01-03-2018 | telephone         | nurse               | 1
2      | 4         | 01-01-2015 | telephone         | doctor              | 2
2      | 4         | 01-01-2015 | home visit        | other               | 4
2      | 4         | 01-12-2021 | null              | null                | null

table mode_domain:

consultation_mode | professional_domain
in person         | doctor             
in person         | nurse              
in person         | other              
telephone         | doctor             
telephone         | nurse              
telephone         | other              
home visit        | doctor             
home visit        | nurse              
home visit        | other              

query:

select
    base.org_id,
    base.person_id,
    base.month,
    mode_domain.consulation_mode,
    mode_domain.profesional_domain,
    coalesce(raw_data.number_consulations, 0) as number_consulations
from
    (select distinct org_id, person_id, month from raw_data) as base
    cross join
    mode_domain
    left join
    raw_data on raw_data.org_id = base.org_id
        and raw_data.person_id = base.person_id
        and raw_data.month = base.month
        and raw_data.consulation_mode = mode_domain.consultation_mode
        and raw_data.professional_domain = mode_domain.professional_domain
/