Sql实现行列转换

查询各部门人数,男员工数,女员工数(要求显示部门名称,总数,男员工数,女员工数)

select d.Name ‘部门名’,COUNT(*) as ‘总人数’,
sum(case e.sex when ‘男’ then 1 else 0 end) ‘男’,
sum(case e.sex when ‘女’ then 1 else 0 end) ‘女’
from Employee e,Department d
where e.DeparID=d.DeparID group by e.DeparID,d.Name

原表格：

转换后：

select Sex 性别,count(*) 总人数,
sum(case DeparID when 1 then 1 else 0 end) 系统管理部,
sum(case DeparID when 2 then 1 else 0 end) 人力资源部,
sum(case DeparID when 3 then 1 else 0 end) 物资部
from Employee group by Sex