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