示例:
现有实体表Test_tb表数据如下:
Name Sex Job
张三 男 法外狂徒
张珊 女 护士
李士 男 教师
王武 男 教师
李施 女 法外狂徒
王舞 女 教师
我想要查出不同性别每个职业有多少人,男性一行,女性一行:
示例:
Sex 法外狂徒 护士 教师
男 1 1 1
女 1 1 1
下面是处理步骤:
1.先设置好列(这里只是将列值处理为标题字段)
--先建一个临时表,放入固定的字段
CREATE TABLE #Test_tb_Temp
(
Sex nvarchar(50)
);
declare @i int,@rowcount int,@NewField nvarchar(20),@addSql nvarchar(max)
set @i=1;
--1.添加固定列的数据到临时表
insert into #Test_tb_Temp select Sex from Test_tb group by Sex
--获取有多少个职业(作为添加标题字段的循环次数)
select top 1 @rowcount=ID from (select ROW_NUMBER() over(order by Job)as ID,Job from Test_tb group by Job )res order by ID desc
while @i<=@rowcount
begin
--获取职业赋值到变量
select @NewField=Job from (select ROW_NUMBER() over(order by Job)as ID,Job from Test_tb group by Job )res where ID=@i
--将职业作为字段添加到临时表中
SET @addSql='ALTER TABLE #Test_tb_Temp ADD ['+@NewField+'] int DEFAULT 0;' EXEC(@addSql)
set @i=@i+1
end
select * from #Test_tb_Temp
这是查询结果
Sex 法外狂徒 护士 教师
男 NULL NULL NULL
女 NULL NULL NULL
第二步就是更新性别中的各个职业的值
添加一个@updatesql nvarchar(max) 变量 拼接处理数据到新加的列.
完整版:
--先建一个临时表,放入固定的字段
CREATE TABLE #Test_tb_Temp
(
Sex nvarchar(50)
);
declare @i int,@rowcount int,@NewField nvarchar(20),@addSql nvarchar(max),@updatesql nvarchar(max)
set @i=1;
--1.添加固定列的数据到临时表
insert into #Test_tb_Temp select Sex from Test_tb group by Sex
--获取有多少个职业(作为添加标题字段的循环次数)
select top 1 @rowcount=ID from (select ROW_NUMBER() over(order by Job)as ID,Job from Test_tb group by Job )res order by ID desc
while @i<=@rowcount
begin
--获取职业赋值到变量
select @NewField=Job from (select ROW_NUMBER() over(order by Job)as ID,Job from Test_tb group by Job )res where ID=@i
--将职业作为字段添加到临时表中
SET @addSql='ALTER TABLE #Test_tb_Temp ADD ['+@NewField+'] int DEFAULT 0;' EXEC(@addSql)
--添加完字段后,立即更新该字段的数据
SET @updatesql='update #Test_tb_Temp set ['+@NewField+']=b.JobPeople from (select Sex,Job,count(Name)JobPeople from Test_tb group by Sex,Job) b
where #Test_tb_Temp.Sex=b.Sex AND b.Job='''+@NewField+''''; EXEC(@updatesql)
set @i=@i+1
end
select * from #Test_tb_Temp
DROP TABLE #Test_tb_Temp
查询结果:
Sex 法外狂徒 护士 教师
男 1 NULL 2
女 1 1 1