动态SQL语句功能介绍
今天在论坛里见到一个关于动态SQL问题，觉得有点意思，于是解答了一下，顺便把我的解决方案转到这里。
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Question:
本人想做一个组合统计，谁可以告诉我怎么实现。
表如下形式：
id sex school government area
----------------------------------------- *** 男 中学 团员 北京 *** 男 大学 党员 上海 *** 女 小学 群众 北京 *** 女 中学 群众 天津 *** 男 小学 党员 上海 *** 女 大学 团员 北京 -------------------------------------------现在想统计出一个列表，列表横轴和纵轴 分别可由以上4个字段中的一个或几个组合而成。实现横纵都可以动态定制。
比如：
/*
school 群众 团员 党员 ------ ---- ----- ----------- 大学 1 1 1 小学 1 1 1 中学 1 1 1 */或着
/*
school 群众 团员 党员 ------ ---- ----- ----------- 北京 1 1 1 上海 1 1 1 天津 1 1 1 */又或者
/*
school 群众 团员 党员 北京 上海 天津 ------ ---- ----- ----------- ---- ----- ----- 大学 1 1 1 1 1 1 小学 1 1 1 1 1 1 中学 1 1 1 */又或者
/*
school 群众 团员 党员 北京 上海 天津 ------ ---- ----- ----------- ---- ----- ----- 大学 1 1 1 1 1 1 小学 1 1 1 1 1 1 中学 1 1 1 1 1 1 男 女 */----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Answer:
--生成测试数据
create table t(id varchar(6),sex varchar(6),school varchar(6),government varchar(6),area varchar(6)) insert into t select '***','男','中学','团员','北京' insert into t select '***','男','大学','党员','上海' insert into t select '***','女','小学','群众','北京' insert into t select '***','女','中学','群众','天津' insert into t select '***','男','小学','党员','上海' insert into t select '***','女','大学','团员','北京' go--创建存储过程
--@str_col：用于横向排列的列，以','作为结束符 --@str_row：用户纵向分组的列，以','作为结束符 create procedure sp_test(@str_col varchar(80),@str_row varchar(80)) as begin declare @sql varchar(8000), @str1 varchar(8000), @str2 varchar(8000), @temp nvarchar(4000), @col varchar(20), @row varchar(20) set @sql ='' set @str1='' while charindex(',',@str_col)>0 begin set @col=left(@str_col,charindex(',',@str_col)-1) set @str_col=stuff(@str_col,1,charindex(',',@str_col),'') set @temp=N'set @s='''' select @s=@s+'',[''+'+@col+'+'']=sum(case '+@col+' when ''''''+'+@col+'+'''''' then 1 else 0 end) '' from t group by '+@col exec sp_executesql @temp,N'@s varchar(8000) out',@str2 out set @str1=@str1+@str2 end while charindex(',',@str_row)>0 begin set @row=left(@str_row,charindex(',',@str_row)-1) set @str_row=stuff(@str_row,1,charindex(',',@str_row),'') set @sql=@sql+' union all select '+@row+' as 项目'+@str1+' from t group by '+@row end set @sql=stuff(@sql,1,11,'') exec(@sql) end go--执行测试 exec sp_test 'school,sex,','government,area,' go
--输出测试结果
/* 项目 大学 小学 中学 男 女 ------ ----------- ----------- ----------- ----------- ----------- 党员 1 1 0 2 0 群众 0 1 1 0 2 团员 1 0 1 1 1 北京 1 1 1 1 2 上海 1 1 0 2 0 天津 0 0 1 0 1 */--删除测试环境
drop table t drop procedure sp_test go