案例1-:
--行列转换示例 --测试数据 create table 表([11] varchar(2),[22] int,[33] int,[44] int,[55] int) insert 表 select 'aa',1,2,3,6 union all select 'bb',0,1,3,5 union all select 'cc',1,2,3,6 union all select 'dd',1,2,3,6 union all select 'ee',1,2,3,6 go --查询处理 declare @s1 varchar(8000),@s2 varchar(8000) ,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000) ,@i varchar(10) select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0' select @s1=@s1+',@'+@i+' varchar(8000)' ,@s2=@s2+',@'+@i+'=''''' ,@s3=@s3+' select @'+@i+'=@'+@i+'+'',[''+[11]+'']=''+cast(['+name+'] as varchar) from 表' ,@s4=@s4+',@'+@i+'=''select ''+substring(@'+@i+',2,8000)' ,@s5=@s5+'+'' union all ''+@'+@i ,@i=cast(@i as int)+1 from syscolumns where object_id('表')=id and colid<>1 select @s1=substring(@s1,2,8000) ,@s2=substring(@s2,2,8000) ,@s4=substring(@s4,2,8000) ,@s5=substring(@s5,16,8000) exec('declare '+@s1+' select '+@s2+@s3+' select '+@s4+' exec('+@s5+')') go --删除测试表 drop table 表 /*--测试结果 aa bb cc dd ee ----------- ----------- ----------- ----------- ----------- 1 0 1 1 1 2 1 2 2 2 3 3 3 3 3 6 5 6 6 6 (所影响的行数为 4 行) --*/
案例2:
--行列互换 /*--有表 indust 200301 200302 200303 ---------- ---------- ---------- ---------- a 111 222 333 b 444 555 666 c 777 888 999 d 789 910 012 --要求得到结果 日期 a b c d ------ ---- ---- ---- ---- 200301 111 444 777 789 200302 222 555 888 910 200303 333 666 999 012 --*/ --创建测试表 create table test(indust varchar(10) ,[200301] varchar(10) ,[200302] varchar(10) ,[200303] varchar(10)) insert test select 'a','111','222','333' union all select 'b','444','555','666' union all select 'c','777','888','999' union all select 'd','789','910','012' go --数据处理 declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000) select @f1='',@f2='',@f3='' select @f1=@f1+',['+indust+']='''+[200301]+'''' ,@f2=@f2+','''+[200302]+'''' ,@f3=@f3+','''+[200303]+'''' from test exec('select 日期=''200301'''+@f1 +' union all select ''200302'''+@f2 +' union all select ''200303'''+@f3) go --删除测试表 select * from test drop table test /*--测试结果 日期 a b c d ------ ---- ---- ---- ---- 200301 111 444 777 789 200302 222 555 888 910 200303 333 666 999 012 --*/
案例3-生成字段名:
--处理示例 --示例数据 create table tb(A int,B int,C int,D int) insert tb select 1,2 ,3 ,4 union all select 5,6 ,7 ,8 union all select 9,10,11,12 go --查询处理 declare @s1 varchar(8000),@s2 varchar(8000) ,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000) ,@i varchar(10) select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0' select @s1=@s1+',@'+@i+' varchar(8000)' ,@s2=@s2+',@'+@i+'=''[字段1]='''''+name+'''''''' ,@s3=@s3+' ,@'+@i+'=@'+@i+'+'',[字段''+cast(@i as varchar)+'']=''+cast(['+name+'] as varchar)' ,@s4=@s4+',@'+@i+'=''select ''+@'+@i ,@s5=@s5+'+'' union all ''+@'+@i ,@i=cast(@i as int)+1 from syscolumns where object_id('tb')=id select @s1=stuff(@s1,1,1,'') ,@s2=stuff(@s2,1,1,'') ,@s4=stuff(@s4,1,1,'') ,@s5=stuff(@s5,1,15,'') exec('declare '+@s1+',@i int select '+@s2+' set @i=1 select @i=@i+1'+@s3+' from tb select '+@s4+' exec('+@s5+')') go --删除测试表 drop table tb /*--测试结果 字段1 字段2 字段3 字段4 ---- ----------- ----------- ----------- A 1 5 9 B 2 6 10 C 3 7 11 D 4 8 12 (所影响的行数为 4 行) --*/
案例4-字段名:
--行列转换示例1 --测试数据 create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1)) insert 表 select '小说',38.0,59.2 union all select '散文',18.9,30.6 union all select '哲学',16.2,10.2 go --查询处理 declare @s1 varchar(8000),@s2 varchar(8000) ,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000) ,@i varchar(10) select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0' select @s1=@s1+',@'+@i+' varchar(8000)' ,@s2=@s2+',@'+@i+'=''性别='''''+name+'''''''' ,@s3=@s3+' ,@'+@i+'=@'+@i+'+'',[''+[类别]+'']=''+cast(['+name+'] as varchar)' ,@s4=@s4+',@'+@i+'=''select ''+@'+@i ,@s5=@s5+'+'' union all ''+@'+@i ,@i=cast(@i as int)+1 from syscolumns where object_id('表')=id and name<>'类别' select @s1=stuff(@s1,1,1,'') ,@s2=stuff(@s2,1,1,'') ,@s3=stuff(@s3,1,4,'') ,@s4=stuff(@s4,1,1,'') ,@s5=stuff(@s5,1,15,'') exec('declare '+@s1+' select '+@s2+' select '+@s3+' from 表 select '+@s4+' exec('+@s5+')') go --删除测试 drop table 表 /*--测试结果 性别 小说 散文 哲学 ---- ----- ----- ----- 男性 38.0 18.9 16.2 女性 59.2 30.6 10.2 (所影响的行数为 2 行) --*/