如何将SQL查询中单列的5类资质数据转为按组织统计的多列格式

解决方案 #

你可以通过条件聚合逻辑实现行转列效果，将CPNT_ID的不同值转为独立列按组织统计持证人数，修改后的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
• 加入DISTINCT是为了避免同一个学生多条同资质完成记录被重复计数，完全匹配你给出的示例统计结果，如果业务允许重复计数可以去掉该关键字
• 最后通过GROUP BY s.ORG_ID按组织维度聚合，即可得到你需要的多组织汇总统计格式

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