declare @s varchar(8000)
set @s=''
select @s=@s+',['+workname+']=sum(case workname when '''+workname+''' then 1 else 0 end)'
from [table]
group by workname
exec('select 性别=sex'+@s+'
from(
select name,workname,sex from [table]
union all
select name,workname,zhenzhi from [table]
union all
select name,workname,zhichen from [table]
union all
select name,workname,xueli from [table]
)a group by sex')
go
--测试数据
create table [table](name varchar(10),workname varchar(10),sex varchar(10))
insert [table] select '汪娜' ,'计供科','女'
union all select '凌开明','技术科1','男'
union all select '周寅' ,'质监科','男'
union all select '朱荷波','八车间','男'
union all select '邬静娜','八车间','女'
union all select '钱丹萍','八车间','女'
union all select '杜丽娟','三车间','女'
union all select '毛易耀','三车间','男'
union all select '王仲秋','四车间','男'
go
--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+workname+']=sum(case workname when '''+workname+''' then 1 else 0 end)'
from [table]
group by workname
exec('select 性别=sex'+@s+'
from [table]
group by sex')
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+workname+']=sum(case workname when '''+workname+''' then 1 else 0 end)'
from [table]
group by workname
exec('select 性别=sex'+@s+'
from [table]
group by sex')