有一个表
A(id,name)
数据
(0001,a),(0001,b),(0001,c),(0002,b),(0002,d),(0003,a),(0003,c),(0003,e)
如何得到
(0001,a->b->c),(0002,b->d),(0003,a->c->e)这样的结果,
要求只能用select解答,不要用存储过程和游标
A(id,name)
数据
(0001,a),(0001,b),(0001,c),(0002,b),(0002,d),(0003,a),(0003,c),(0003,e)
如何得到
(0001,a->b->c),(0002,b->d),(0003,a->c->e)这样的结果,
要求只能用select解答,不要用存储过程和游标
from (select id,name,(row_number() over(order by id,name desc) + dense_rank() over(order by id)) rn,
max(name) over(partition by id) qs
from A
)
start with name = qs
connect by rn-1 = prior rn
group by id
insert a values('0001','a')
insert a values('0001','b')
insert a values('0001','c')
insert a values('0002','b')
insert a values('0002','d')
insert a values('0003','a')
insert a values('0003','c')
insert a values('0003','e')create function f_getString(@id varchar(4))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+'->'+[name] from a where id=@id
return stuff(@s,1,2,'')end
goselect id,[name]=dbo.f_getString(id) from a group by id