select id,max(case num when 17 then 17 else 0 end),max(case num when 22 then 22 else 0 end) from table1 group by id
declare @sql varchar(8000) set @sql = 'select id' select @sql = @sql+',max(case num when '+num+' then '+num+' else 0 end)' from (select distinct num as num from table1) t; exec(@sql);
declare @sql varchar(8000) set @sql = 'select id' select @sql = @sql+',max(case num when '+num+' then '+num+' else 0 end)' from (select distinct num as num from table1) t; set @sql=@sql+' from table1 group by id'; exec(@sql);
可以考虑into一个表: set @sql=@sql+' into table2 from table1 group by id';
set @sql = 'select id'
select @sql = @sql+',max(case num when '+num+' then '+num+' else 0 end)' from (select distinct num as num from table1) t;
exec(@sql);
set @sql = 'select id'
select @sql = @sql+',max(case num when '+num+' then '+num+' else 0 end)' from (select distinct num as num from table1) t;
set @sql=@sql+' from table1 group by id';
exec(@sql);
set @sql=@sql+' into table2 from table1 group by id';