如何在SQL中扩展组

Question

我该如何从：

organisation | individual | month      | number_consultations | mode         | professional | setting
A            | A01        | 2016-01-01 | 7                    | face-to-face | nurse        | group1
A            | A01        | 2016-01-01 | 3                    | telephone    | doctor       | group1

.....为

organisation | individual | month      | number_consultations | mode          | professional    | setting
A            | A01        | 2016-01-01 | 7                    | face-to-face  | nurse           | group1
A            | A01        | 2016-01-01 | 0                    | telephone     | nurse           | group1
A            | A01        | 2016-01-01 | 0                    | homevisit     | nurse           | group1
A            | A01        | 2016-01-01 | 0                    | digital       | nurse           | group1
A            | A01        | 2016-01-01 | 0                    | face-to-face  | doctor          | group1
A            | A01        | 2016-01-01 | 3                    | telephone     | doctor          | group1
A            | A01        | 2016-01-01 | 0                    | homevisit     | doctor          | group1
A            | A01        | 2016-01-01 | 0                    | digital       | doctor          | group1
A            | A01        | 2016-01-01 | 0                    | face-to-face  | otherdirectcare | group1
A            | A01        | 2016-01-01 | 0                    | telephone     | otherdirectcare | group1
A            | A01        | 2016-01-01 | 0                    | homevisit     | otherdirectcare | group1
A            | A01        | 2016-01-01 | 0                    | digital       | otherdirectcare | group1
A            | A01        | 2016-01-01 | 0                    | face-to-face  | other           | group1
A            | A01        | 2016-01-01 | 0                    | telephone     | other           | group1
A            | A01        | 2016-01-01 | 0                    | homevisit     | other           | group1
A            | A01        | 2016-01-01 | 0                    | digital       | other           | group1
A            | A01        | 2016-01-01 | 0                    | face-to-face  | nurse           | group2
A            | A01        | 2016-01-01 | 0                    | telephone     | nurse           | group2
A            | A01        | 2016-01-01 | 0                    | homevisit     | nurse           | group2
A            | A01        | 2016-01-01 | 0                    | digital       | nurse           | group2
A            | A01        | 2016-01-01 | 0                    | face-to-face  | doctor          | group2
A            | A01        | 2016-01-01 | 0                    | telephone     | doctor          | group2
A            | A01        | 2016-01-01 | 0                    | homevisit     | doctor          | group2
A            | A01        | 2016-01-01 | 0                    | digital       | doctor          | group2
A            | A01        | 2016-01-01 | 0                    | face-to-face  | otherdirectcare | group2
A            | A01        | 2016-01-01 | 0                    | telephone     | otherdirectcare | group2
A            | A01        | 2016-01-01 | 0                    | homevisit     | otherdirectcare | group2
A            | A01        | 2016-01-01 | 0                    | digital       | otherdirectcare | group2
A            | A01        | 2016-01-01 | 0                    | face-to-face  | other           | group2
A            | A01        | 2016-01-01 | 0                    | telephone     | other           | group2
A            | A01        | 2016-01-01 | 0                    | homevisit     | other           | group2
A            | A01        | 2016-01-01 | 0                    | digital       | other           | group2

.....

对于每个组织，个人和月份，我想扩展：

• 模式（面对面，电话，家庭视频，数字）

• 专业人士（护士，医生，其他直达电脑，其他）

• 设置（group1，group2，group3，group4）

如何在SQL中执行此操作？

Answer 1

您可以使用多个交叉加入 s：

with modes(mode) as (
   select 'face-to-face'
   union all
   select 'telephone'
   union all
   select 'homevisit'
   union all
   select 'digital'
),
professionals(p) as (
   select 'nurse'
   union all
   select 'doctor'
   union all
   select 'otherdirectcare'
   union all
   select 'other'
),
settings(s) as (
   select 'group1'
   union all
   select 'group2'
   union all
   select 'group3'
   union all
   select 'group4'
)
select t1.*, coalesce(t2.number_consultations, 0) 
from (select t.organization, t.individual, t.month, m.mode, p.p, s.s 
      from tbl t cross join modes m cross join professionals p cross join settings s) t1 
left join tbl t2 on t1.mode = t2.mode and t1.p = t2.professional and t1.s = t2.setting

。