sql行列统计后，行列转换显示

 

----查询每个单位的该专业人员数量
select unitid,majorclass, count(id) cc from person
where unitid like'340%' group by unitid,majorclass
 
unitid majorclass cc[单位编号，专业，人数]
340201 2 97
340202 2 4

----行转列
   select unitid '单位编号',unit.unitname '单位名称'
 ,sum(case majorclass when '1' then cc else 0 end) '通信'
 ,sum(case majorclass when '2' then cc else 0 end) '信号'
 ,sum(case majorclass when '71' then cc else 0 end) '电力'
 ,sum(case majorclass when '64' then cc else 0 end) '测量'
 ,sum(case majorclass when '65' then cc else 0 end) '桥梁'
 ,sum(cc) '合计'
 from
 (
  select unitid,majorclass, count(id) cc from person where unitid like'340%' group by unitid,majorclass
  )
  b,unit where b.unitID=unit.unitcode  GROUP by unitid,unitname
 
单位编号 单位名称 通信 信号 电力 测量 桥梁 合计
340201 二局一公司 0 97 0 0 0 97
340202 二局二公司 0 4 0 0 0 4