declare @sql varchar(6000)
set @sql=''
select @sql=@sql+',case bbb when '+cast(bbb as varchar(20))+' then '+cast(bbb as varchar(20))+' else null end as ['+cast(bbb as varchar(20))+']'
from (select distinct bbb from a) as xset @sql='select *'+@sql+' from a'exec(@sql)
set @sql=''
select @sql=@sql+',case bbb when '+cast(bbb as varchar(20))+' then '+cast(bbb as varchar(20))+' else null end as ['+cast(bbb as varchar(20))+']'
from (select distinct bbb from a) as xset @sql='select *'+@sql+' from a'exec(@sql)
declare @fd varchar(8000),@s varchar(8000)
select @fd='',@s=''
select @fd=@fd+'
alter table A add ['+cast(bbb as varchar)+'] varchar(10)'
,@s=@s+',['+cast(bbb as varchar)+']=case bbb when '
+cast(bbb as varchar)+' then '''+cast(bbb as varchar)
+''' else '''' end'
from A group by bbb
set @s=substring(@s,2,8000)
exec(@fd)
exec('update A set '+@s)--显示处理结果
select * from A
create table A(aaa int,bbb int,ccc int)
insert A select 1,2,5
union all select 2,3,6
union all select 3,4,7
union all select 4,4,8
union all select 5,2,9
go--数据处理
declare @fd varchar(8000),@s varchar(8000)
select @fd='',@s=''
select @fd=@fd+'
alter table A add ['+cast(bbb as varchar)+'] varchar(10)'
,@s=@s+',['+cast(bbb as varchar)+']=case bbb when '
+cast(bbb as varchar)+' then '''+cast(bbb as varchar)
+''' else '''' end'
from A group by bbb
set @s=substring(@s,2,8000)
exec(@fd)
exec('update A set '+@s)--显示处理结果
select * from A
go--删除测试
drop table A/*--i测试结果aaa bbb ccc 2 3 4
----------- ----------- ----------- ---------- ---------- ----------
1 2 5 2
2 3 6 3
3 4 7 4
4 4 8 4
5 2 9 2 (所影响的行数为 5 行)--*/
declare @s varchar(8000)
set @s=''
select @s=@s+',['+cast(bbb as varchar)+']=case bbb when '
+cast(bbb as varchar)+' then '''+cast(bbb as varchar)
+''' else '''' end'
from A group by bbb
exec('select *'+@s+' into B from A')--显示B表
select * from B
create table A(aaa int,bbb int,ccc int)
insert A select 1,2,5
union all select 2,3,6
union all select 3,4,7
union all select 4,4,8
union all select 5,2,9
go--数据处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+cast(bbb as varchar)+']=case bbb when '
+cast(bbb as varchar)+' then '''+cast(bbb as varchar)
+''' else '''' end'
from A group by bbb
exec('select *'+@s+' into B from A')--显示B表
select * from B
go--删除测试
drop table A/*--i测试结果aaa bbb ccc 2 3 4
----------- ----------- ----------- ---- ---- ----
1 2 5 2
2 3 6 3
3 4 7 4
4 4 8 4
5 2 9 2 (所影响的行数为 5 行)
--*/
declare @sql varchar(8000)
set @sql='select aaa,bbb,ccc,'
select @sql=@sql+'['+cast(bbb as varchar(10))+']=(case when bbb='+cast(bbb as varchar(10))+' then bbb else null end),' from #tmp
set @sql=substring(@sql,1,len(@sql)-1)+' from a '
print @SQL
exec(@sql)