create table t(id varchar(10),num int) insert t select 'A',55 union select 'A',44 union select 'A',33 union select 'A',22 create proc p1 @id varchar(10) as begin declare @str varchar(8000) select @str='select id ' select @str=@str+' , max(case when num='+rtrim(num)+' then num else 0 end) as num'+rtrim(num) from t where id=@id
select @str=@str+' from t group by id ' execute(@str) end go exec p1 'A' drop proc p1 drop table t /* 结果 id num22 num33 num44 num55 ---------- ----------- ----------- ----------- ----------- A 22 33 44 55(所影响的行数为 1 行) */
create table #t (ID int,num varchar(10)) insert into #t select 1,'a' insert into #t select 1,'b' insert into #t select 1,'d' insert into #t select 1,'c' insert into #t select 1,'e' go create proc testt as declare @sql varchar(8000) select @sql = isnull(@sql,'select ID ') + ' ,max(case num when '''+ num+''' then num end) as ' +num from #t group by num select @sql =@sql +' from #t group by ID' exec(@sql) go exec testt
insert t
select 'A',55
union select 'A',44
union select 'A',33
union select 'A',22
create proc p1
@id varchar(10)
as
begin
declare @str varchar(8000) select @str='select id '
select @str=@str+' , max(case when num='+rtrim(num)+' then num else 0 end) as num'+rtrim(num)
from t where id=@id
select @str=@str+' from t group by id '
execute(@str)
end
go
exec p1 'A'
drop proc p1
drop table t
/* 结果
id num22 num33 num44 num55
---------- ----------- ----------- ----------- -----------
A 22 33 44 55(所影响的行数为 1 行)
*/
create table #t (ID int,num varchar(10))
insert into #t select 1,'a'
insert into #t select 1,'b'
insert into #t select 1,'d'
insert into #t select 1,'c'
insert into #t select 1,'e'
go
create proc testt
as
declare @sql varchar(8000)
select @sql = isnull(@sql,'select ID ') + ' ,max(case num when '''+ num+''' then num end) as ' +num
from #t group by num
select @sql =@sql +' from #t group by ID'
exec(@sql)
go
exec testt