insert into b select 1,100,'abc',111 insert into b select 2,100,'edf',222 insert into b select 3,100,'ghi',333
declare @s varchar(8000) set @s =''
select @s = @s +', sum(case code when ''' + code +''' then value else 0 end ) as '+ code from b group by code order by code set @s = 'select id ' + @s +' from b group by id '
exec(@s)
declare @s varchar(8000) set @s =''
select @s = @s +', sum(case code when ''' + code +''' then value else 0 end ) as '+ code from b group by code order by code set @s = 'select id ' + @s +' from b group by id '
exec(@s)上面的这段代码是怎样运行的?在什么开发环境下运行的?
create table T(no int, id int, code varchar(10), value int) insert T select 1, 100, 'abc', 111 union all select 2, 100, 'edf', 222 union all select 3, 100, 'ghi', 333 declare @sql varchar(8000) set @sql='select id,' select @sql=@sql+quotename(code)+'=max(case when code='+quotename(code, '''')+' then value end),' from T group by code select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from T group by id ' exec(@sql)--result id abc edf ghi ----------- ----------- ----------- ----------- 100 111 222 333
insert into b select 1,100,'abc',111
insert into b select 2,100,'edf',222
insert into b select 3,100,'ghi',333
declare @s varchar(8000)
set @s =''
select @s = @s +', sum(case code when ''' + code +''' then value else 0 end ) as '+ code
from b group by code order by code
set @s = 'select id ' + @s +' from b group by id '
exec(@s)
set @s =''
select @s = @s +', sum(case code when ''' + code +''' then value else 0 end ) as '+ code
from b group by code order by code
set @s = 'select id ' + @s +' from b group by id '
exec(@s)上面的这段代码是怎样运行的?在什么开发环境下运行的?
create table T(no int, id int, code varchar(10), value int)
insert T select 1, 100, 'abc', 111
union all select 2, 100, 'edf', 222
union all select 3, 100, 'ghi', 333
declare @sql varchar(8000)
set @sql='select id,'
select @sql=@sql+quotename(code)+'=max(case when code='+quotename(code, '''')+' then value end),'
from T group by code
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from T group by id '
exec(@sql)--result
id abc edf ghi
----------- ----------- ----------- -----------
100 111 222 333