行转列老问题？

ma_yan_jun_78 2008-03-25 10:33:01

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 n

declare @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 ')
我的代码与我相的结果不符，请各们朋友帮我解决一下，谢谢！

...全文

31 3 点赞打赏收藏举报

写回复

3 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

gahade 2008-03-25

看回复专用

打赏
举报

回复赞

$扫地僧$ 2008-03-25

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

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)

打赏
举报

回复赞

gahade 2008-03-25



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

*/