create table Tb(id int,iid bigint)
insert into Tb
select 1,1
union all select 1,2
union all select 1,3
--.....n
union all select 2,1
union all select 2,2
union all select 2,3
--......n我想要的结果:
id , iid1,iid2,iid3,iidn
1 1 2 3 n
2 1 2 3 ndeclare @s varchar(8000)
select @s=' select id'
select @s=@s+',[技能'+cast([iid] as varchar) +']=case [iid] when '+ cast([iid] as varchar)+' then iid end'from a
exec (@s+' from a ')
我的代码与我相的结果不符,请各们朋友帮我解决一下,谢谢!
create table Tb(id int,iid bigint)
insert into Tb
select 1,1
union all select 1,2
union all select 1,3
union all select 2,1
union all select 2,2
union all select 2,3 declare @s varchar(8000)
set @s=''
select @s=@s+',max(case when iid='+rtrim(iid)+' then iid else 0 end) as [iid'+rtrim(iid)+']'
from (select distinct iid from Tb)t
exec('select id'+@s+' from Tb group by id')
/*
id iid1 iid2 iid3
----------- -------------------- -------------------- --------------------
1 1 2 3
2 1 2 3
*/
insert into Tb
select 1,1
union all select 1,2
union all select 1,3
--.....n
union all select 2,1
union all select 2,2
union all select 2,3
declare @T_SQL varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL + ' Max(case when iid=' + cast(iid as varchar) + 'then iid else 0 end) as iid' +cast(iid as varchar) +',' from (select DISTINCT iid from Tb) T
set @T_SQL=left(@T_SQL,len(@T_SQL)-1)
set @T_SQL='select id,' + @T_SQL + ' from Tb group by id'
exec (@T_SQL)