create table #T(区域 varchar(10),性别 varchar(10),学历 varchar(10),职称 varchar(10),年龄 int)declare @s varchar(8000)
set @s = ''select
@s = @s+',[学历('+QUOTENAME(学历)+')]=sum(case 学历 when '+QUOTENAME(学历)+' then 1 else 0 end)'
from
#T group by 学历select
@s = @s+',[职称('+QUOTENAME(职称)+')]=sum(case 职称 when '+QUOTENAME(职称)+' then 1 else 0 end)'
from
#T group by 职称set @s = 'select 区域
,总人数=count(*)
,[性别(男)]=sum(case 性别 when ''男'' then 1 else 0 end)
,[性别(女)]=sum(case 性别 when ''女'' then 1 else 0 end)'
+@s
+',[年龄(20以下)]=sum(case when 年龄<20 then 1 else 0 end)'
+',[年龄(20--29)]=sum(case when 年龄 between 20 and 29 then 1 else 0 end)'
+',[年龄(30--39)]=sum(case when 年龄 between 30 and 39 then 1 else 0 end)'
+',[年龄(40--49)]=sum(case when 年龄 between 40 and 49 then 1 else 0 end)'
+',[年龄(50以上)]=sum(case when 年龄>50 then 1 else 0 end)'
+' from #T group by 区域'exec(@s)drop table #T
set @s = ''select
@s = @s+',[学历('+QUOTENAME(学历)+')]=sum(case 学历 when '+QUOTENAME(学历)+' then 1 else 0 end)'
from
#T group by 学历select
@s = @s+',[职称('+QUOTENAME(职称)+')]=sum(case 职称 when '+QUOTENAME(职称)+' then 1 else 0 end)'
from
#T group by 职称set @s = 'select 区域
,总人数=count(*)
,[性别(男)]=sum(case 性别 when ''男'' then 1 else 0 end)
,[性别(女)]=sum(case 性别 when ''女'' then 1 else 0 end)'
+@s
+',[年龄(20以下)]=sum(case when 年龄<20 then 1 else 0 end)'
+',[年龄(20--29)]=sum(case when 年龄 between 20 and 29 then 1 else 0 end)'
+',[年龄(30--39)]=sum(case when 年龄 between 30 and 39 then 1 else 0 end)'
+',[年龄(40--49)]=sum(case when 年龄 between 40 and 49 then 1 else 0 end)'
+',[年龄(50以上)]=sum(case when 年龄>50 then 1 else 0 end)'
+' from #T group by 区域'exec(@s)drop table #T
insert into #T select '上海','男','大本','中级工程师',25
insert into #T select '上海','男','大专','初级工程师',19
insert into #T select '上海','女','大本','中级工程师',28
insert into #T select '上海','女','博士','高级工程师',33
insert into #T select '上海','女','大本','初级工程师',25
insert into #T select '北京','男','博士','中级工程师',30
insert into #T select '北京','女','大本','中级工程师',25
insert into #T select '北京','女','博士','高级工程师',50
insert into #T select '北京','女','大本','高级工程师',45
insert into #T select '北京','男','大专','中级工程师',35declare @s varchar(8000)
set @s = ''select
@s = @s+',[学历('+rtrim(学历)+')]=sum(case 学历 when '''+rtrim(学历)+''' then 1 else 0 end)'
from
#T group by 学历select
@s = @s+',[职称('+rtrim(职称)+')]=sum(case 职称 when '''+rtrim(职称)+''' then 1 else 0 end)'
from
#T group by 职称set @s = 'select 区域
,总人数=count(*)
,[性别(男)]=sum(case 性别 when ''男'' then 1 else 0 end)
,[性别(女)]=sum(case 性别 when ''女'' then 1 else 0 end)'
+@s
+',[年龄(20以下)]=sum(case when 年龄<20 then 1 else 0 end)'
+',[年龄(20--29)]=sum(case when 年龄 between 20 and 29 then 1 else 0 end)'
+',[年龄(30--39)]=sum(case when 年龄 between 30 and 39 then 1 else 0 end)'
+',[年龄(40--49)]=sum(case when 年龄 between 40 and 49 then 1 else 0 end)'
+',[年龄(50以上)]=sum(case when 年龄>50 then 1 else 0 end)'
+' from #T group by 区域'exec(@s)print @s
drop table #T
set @s = ''select
@s = @s+',[学历('+rtrim(XueLi)+')]=sum(case XueLi when '''+rtrim(XueLi)+''' then 1 else 0 end)'
from
Init_ZJGcs group by XueLiselect
@s = @s+',[职称('+rtrim(ZhiCheng)+')]=sum(case ZhiCheng when '''+rtrim(ZhiCheng)+''' then 1 else 0 end)'
from
Init_ZJGcs group by ZhiChengset @s = 'select 管辖市=City
,总人数=count(1)
,[性别(男)]=sum(case Sex when ''男'' then 1 else 0 end)
,[性别(女)]=sum(case Sex when ''女'' then 1 else 0 end)'
+@s
+' from Init_ZJGcs group by City'exec(@s)print @s
@s = @s+',[学历('+rtrim(isnull(XueLi,'null'))+')]=sum(case isnull(XueLi,'null'))+when '''+rtrim(XueLi)+''' then 1 else 0 end)'
from
Init_ZJGcs group by XueLi
@s = @s+',[学历('+rtrim(XueLi)+')]=sum(case XueLi when '''+rtrim(XueLi)+''' then 1 else 0 end)'
from
Init_ZJGcs where XueLi is not null group by XueLi
--------------------------------------------------------------------------------------------------------
declare @s varchar(8000)
set @s = ''select
@s = @s+',[学历('+rtrim(XueLi)+')]=sum(case XueLi when '''+rtrim(XueLi)+''' then 1 else 0 end)'
from
Init_ZJGcs where XueLi is not null group by XueLiset @s = @s+',[学历(null)]=sum(case when XueLi is null then 1 else 0 end)'select
@s = @s+',[职称('+rtrim(ZhiCheng)+')]=sum(case ZhiCheng when '''+rtrim(ZhiCheng)+''' then 1 else 0 end)'
from
Init_ZJGcs group by ZhiChengset @s = 'select 管辖市=City
,总人数=count(1)
,[性别(男)]=sum(case Sex when ''男'' then 1 else 0 end)
,[性别(女)]=sum(case Sex when ''女'' then 1 else 0 end)'
+@s
+' from Init_ZJGcs group by City'exec(@s)print @s
----------------------------------------------------------------------------------------
--修改一下,顺便加点测试数据,执行结果在查询分析器里自己看:create table #T(区域 varchar(10),性别 varchar(10),学历 varchar(10),职称 varchar(10),年龄 int)
insert into #T select '上海','男','大本','中级工程师',25
insert into #T select '上海','男','大专','初级工程师',19
insert into #T select '上海','女','大本','中级工程师',28
insert into #T select '上海','女','博士','高级工程师',33
insert into #T select '上海','女','大本','初级工程师',25
insert into #T select '北京','男','博士','中级工程师',30
insert into #T select '北京','女','大本','中级工程师',25
insert into #T select '北京','女','博士','高级工程师',50
insert into #T select '北京','女','大本','高级工程师',45
insert into #T select '北京','男','大专','中级工程师',35declare @s varchar(8000)
set @s = ''select
@s = @s+',[学历('+rtrim(学历)+')]=rtrim(sum(case 学历 when '''+rtrim(学历)+''' then 1 else 0 end))+''/''+rtrim(sum(case 学历 when '''+rtrim(学历)+''' then 1 else 0 end)*100/count(*))+''%'''
from
#T group by 学历select
@s = @s+',[职称('+rtrim(职称)+')]=rtrim(sum(case 职称 when '''+rtrim(职称)+''' then 1 else 0 end))+''/''+rtrim(sum(case 职称 when '''+rtrim(职称)+''' then 1 else 0 end)*100/count(*))+''%'''
from
#T group by 职称set @s = 'select 区域
,总人数=count(*)
,[性别(男)]=rtrim(sum(case 性别 when ''男'' then 1 else 0 end))+''/''+rtrim(sum(case 性别 when ''男'' then 1 else 0 end)*100/count(*))+''%''
,[性别(女)]=rtrim(sum(case 性别 when ''女'' then 1 else 0 end))+''/''+rtrim(sum(case 性别 when ''女'' then 1 else 0 end)*100/count(*))+''%'''
+@s+' from #T group by 区域'exec(@s)drop table #T