字段转换、合并结果集

需求：

原始sql：需求的结果集为一列字段

SELECT
  p_d_region.TName,
     entbase.enba_state,
COUNT(entbase.enba_state)coun

FROM
     (select * from tb_z_entbase where tb_z_entbase.quan_id='3')  entbase
RIGHT JOIN tb_z_entmain ON entbase.Enba_qid = tb_z_entmain.Enma_id
RIGHT JOIN tb_z_finance ON tb_z_entmain.Enma_id = tb_z_finance.Fina_id
RIGHT JOIN p_d_region  ON p_d_region.TID = entbase.Enba_reg_addr_prov
WHERE p_d_region.TLevel='1'  AND entbase.enba_state IS NOT NULL
GROUP BY
entbase.enba_state,p_d_region.TName

最终sql：

SELECT
  p_d_region.TID,p_d_region.TName, count(entbase.Enba_state) zs,
count((CASE entbase.enba_state WHEN '编辑中' THEN '编辑中' END)) bjz,
count((CASE entbase.enba_state WHEN '已提交' THEN '已提交' END)) ytj,
count((CASE entbase.enba_state WHEN '退回修改' THEN '退回修改' END)) thxg,
count((CASE entbase.enba_state WHEN '省级工商联审核' THEN '省级工商联审核' END)) sjgglsh,
count((CASE entbase.enba_state WHEN '全国工商联审核' THEN '全国工商联审核' END)) qggslsh,
count((CASE entbase.enba_state WHEN '已删除' THEN '已删除' END)) ysc
FROM
(select * from tb_z_entbase where tb_z_entbase.quan_id='3')  entbase
LEFT JOIN tb_z_entmain ON entbase.Enba_qid = tb_z_entmain.Enma_id
LEFT JOIN tb_z_finance ON entbase.Enba_qid = tb_z_finance.Fina_id
LEFT JOIN tb_z_quantum ON entbase.Quan_id = tb_z_quantum.Quan_id
LEFT JOIN p_dept ON entbase.Enba_acfic = p_dept.DeptID
RIGHT JOIN p_d_region  ON p_d_region.TID = entbase.Enba_reg_addr_prov
WHERE p_d_region.TLevel='1'  AND entbase.enba_state IS NOT NULL AND p_dept.NodePath LIKE '/0/%'
GROUP BY
p_d_region.TName,p_d_region.TID
order by p_d_region.TID

最终效果：