【Transact-SQL】通过递归来实现：将多条记录的某个字段的值，用斜杠拼接在一起...

 需要把表@tt中的idd相同的记录中tchar字段的内容拼接在一起，结果如下

1,'abc/xyz/ggg'

2,'111/soft'

 

实验代码如下：

 

declare @tt table(idd int,tchar varchar(10))

insert into @tt    
select 1,'abc' union all
select 1,'xyz' union all
select 1,'ggg' union all
select 2,'111' union all
select 2,'soft' 


;with c
as
(
select idd,
       tchar,
	   row_number() over(partition by idd order by tchar) as row
from @tt 
),

cc   --递归CTE
as
(
	select idd,
           cast(tchar as varchar(1000)) as ch,
		   row
	from c 
	where row = 1

    union all

	select cc.idd,
           ch=cast(cc.ch+'/'+c.tchar as varchar(1000)),
           c.row
	from cc
	inner join c 
			on c.idd = cc.idd
			   and c.row = cc.row+1
),
ccc
as
(
select idd,ch,row,
       row_number() over(partition by idd order by row desc) as rownum
from cc
)

select * from ccc 
where rownum=1
/*
idd	ch	        row	rownum
1	abc/ggg/xyz	3	1
2	111/soft	2	1
*/

 

转载于:https://www.cnblogs.com/momogua/archive/2011/11/19/8304639.html