表
GrpID ID
gid1 001
gid1 002
gid1 003
gid2 001
gid2 021
gid2 011
gid3 211
gid3 011
.....ID 的值 不确定 但每组GrpID 最多 有3个 ID 也可能少于 3 个要 得到如下结果
GrpID ID1 ID2 ID3
grd1 001 002 003
grd2 001 021 011
grd3 211 011在线等 救命 先谢了
GrpID ID
gid1 001
gid1 002
gid1 003
gid2 001
gid2 021
gid2 011
gid3 211
gid3 011
.....ID 的值 不确定 但每组GrpID 最多 有3个 ID 也可能少于 3 个要 得到如下结果
GrpID ID1 ID2 ID3
grd1 001 002 003
grd2 001 021 011
grd3 211 011在线等 救命 先谢了
a.GrpID,
ID1=max(case Num when 1 then ID end),
ID2=max(case Num when 2 then ID end),
ID3=max(case Num when 3 then ID end)
from
(select t.*,(select count(*) from 表 where GrpID=t.GrpID and ID<=t.ID) as Num from 表 t) a
group by
a.GrpID
insert into @t select 'gid1','001'
insert into @t select 'gid1','002'
insert into @t select 'gid1','003'
insert into @t select 'gid2','001'
insert into @t select 'gid2','021'
insert into @t select 'gid2','011'
insert into @t select 'gid3','211'
insert into @t select 'gid3','011'select
a.GrpID,
ID1=max(case Num when 1 then ID end),
ID2=max(case Num when 2 then ID end),
ID3=max(case Num when 3 then ID end)
from
(select t.*,(select count(*) from @t where GrpID=t.GrpID and ID<=t.ID) as Num from @t t) a
group by
a.GrpID/*
GrpID ID1 ID2 ID3
------ ------ ------ ------
gid1 001 002 003
gid2 001 011 021
gid3 011 211 NULL
*/