比如说我有一个表 A
id code
1 a
1 b
1 c
2 d
2 f要把数据选出来插到新表 B中 ,显示的是这个样子
id code1 code2 code3
1 a b c
2 d f null各位帮帮忙吧
id code
1 a
1 b
1 c
2 d
2 f要把数据选出来插到新表 B中 ,显示的是这个样子
id code1 code2 code3
1 a b c
2 d f null各位帮帮忙吧
调试欢乐多
insert into @t select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 2,'d'
union all select 2,'f'
select id,
code1=max(case id_t when 1 then code else null end),
code2=max(case id_t when 2 then code else null end),
code3=max(case id_t when 3 then code else null end)
from
(select *,[id_t]=(select count(*)+1 from @t where id=a.id and code<a.code) from @t a)x
group by id
insert into @t select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 2,'d'
union all select 2,'f'
select id,
code1=max(case id_t when 1 then code else null end),
code2=max(case id_t when 2 then code else null end),
code3=max(case id_t when 3 then code else null end)
into B
from
(select *,[id_t]=(select count(*)+1 from @t where id=a.id and code<a.code) from @t a)x
group by idselect * from Bdrop table B--插入新表B
1 a
1 b
1 c
2 d
2 f
对上表加一个字段表示每个重复ID进行编号
如
id code No
1 a 1
1 b 2
1 c 3
2 d 1
2 f 2
下面就只是行转列的问题了
insert into @t select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 2,'d'
union all select 2,'f'select [id_t]=identity(int,1,1),* into # from @t
select * from #
select id,
code1=max(case when (case when id_t>3 then (id_t-3) else id_t end)%3=1 then code else null end),
code2=max(case when (case when id_t>3 then (id_t-3) else id_t end)%3=2 then code else null end),
code3=max(case when (case when id_t>3 then (id_t-3) else id_t end)%3=0 then code else null end)
from
#
group by iddrop table #