如何将单列中的5类CPNT_ID数据转为5个独立列实现SQL行转列统计

需求说明 #

你当前的原始查询返回4个字段：CPNT_ID、Org_Id、Stud ID、Compl_Dte，仅支持单组织查询。需改造为按组织分组的行转列统计：

• 分组维度：Org_Id（组织ID）
• 统计列：将CPNT_ID下的Trainee、SvcTech、CrewChief、SvcCoord、Appr五个类别分别作为独立列
• 统计规则：统计每个组织下对应类别的已完成认证人数，无数据时返回0

改造后SQL代码 #

select 
    s.ORG_ID AS Organization,
    COUNT(DISTINCT CASE WHEN cpnt.cpnt_id = 'Trainee' THEN pc.stud_id END) AS Trainee,
    COUNT(DISTINCT CASE WHEN cpnt.cpnt_id = 'SvcTech' THEN pc.stud_id END) AS SvcTech,
    COUNT(DISTINCT CASE WHEN cpnt.cpnt_id = 'CrewChief' THEN pc.stud_id END) AS CrewChief,
    COUNT(DISTINCT CASE WHEN cpnt.cpnt_id = 'SvcCoord' THEN pc.stud_id END) AS SvcCoord,
    COUNT(DISTINCT CASE WHEN cpnt.cpnt_id = 'Appr' THEN pc.stud_id END) AS Appr
from 
    pa_stud_program sp,
    pa_program p,
    pa_student s,
    pa_stud_cpnt pc,
    ps_program_type pt,
    pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
    and pc.compl_dte is not null
    and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
    and s.jp_id in ('1801','1805','1810','1811')
    and s.EMP_STAT_ID = 'Active'
    AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
    AND cpnt.CPNT_ID         = p.CPNT_ID
    AND cpnt.REV_DTE         = p.REV_DTE
    AND pc.STUD_ID           = sp.STUD_ID
    AND sp.stud_id           = s.STUD_ID
    AND pc.CPNT_ID           = sp.CPNT_ID
    AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
    AND pc.REV_DTE           = sp.REV_DTE
    AND pc.seq_num           = sp.seq_num
    AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE   
    /** and s.PERSON_ID_EXTERNAL  in [UserSearch]*/ 
GROUP BY s.ORG_ID
ORDER BY s.ORG_ID

逻辑说明 #

• 采用条件聚合实现行转列：通过CASE WHEN匹配对应认证类别，匹配成功返回人员ID，不匹配返回NULL
• COUNT统计时会自动忽略NULL值，无符合条件的数据时统计结果自动为0，无需额外判空处理
• 用COUNT(*DISTINCT*)避免同一个人同个认证存在多条完成记录时被重复统计，若你的业务场景不会出现重复数据可去掉DISTINCT提升查询效率
• 最终结果按ORG_ID分组排序，每行对应一个组织的五类认证人员统计数据

内容的提问来源于stack exchange，提问作者Mr Grimm