高難度sql轉換語句，歡迎挑戰

--示例数据

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 行）

--*/

create table tb(a varchar(10),b varchar(10),c varchar(10))

insert into tb values('2004/11/09', '0000000001', '16:35') 

insert into tb values('2004/11/09', '0000000001', '16:38') 

insert into tb values('2004/11/09', '0000000001', '16:44') 

insert into tb values('2004/11/09', '0000000002', '20:30') 

insert into tb values('2004/11/09', '0000000002', '22:22') 

insert into tb values('2004/11/09', '0000000001', '23:36') 

insert into tb values('2004/11/11', '0000000003', '23:42') 

insert into tb values('2004/11/09', '0000000001', '23:47') 

insert into tb values('2004/11/09', '0000000003', '00:11') 

insert into tb values('2004/11/10', '0000000001', '00:49') 

go



declare @sql varchar(8000)

set @sql = 'select a'

select @sql = @sql + ' , max(case b when ''' + b + ''' then c else '' '' end) [' + b + ']'

from (select distinct b from tb) as a

set @sql = @sql + ' from (select px = (select count(1) from tb where a = t.a and b = t.b and c < t.c) + 1 , * from tb t) m group by a,px order by a,px'

exec(@sql) 



drop table tb



/*

a          0000000001 0000000002 0000000003 

---------- ---------- ---------- ---------- 

2004/11/09 16:35      20:30      00:11

2004/11/09 16:38      22:22       

2004/11/09 16:44                  

2004/11/09 23:36                  

2004/11/09 23:47                  

2004/11/10 00:49                  

2004/11/11                       23:42

*/

老大的.



--行列转换示例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 行）

--*/