如何合并某列

 近来工作中遇到一个问题：

1. 表结构

Entity_No, Name

E1001       Name1001_1

E1001       Name1001_2

E1001       Name1001_3

E1002       Name1002_1

 

2. 要求以下结果：

Entity_No, Name

E1001      Name1001_1,Name1001_2,Name1001_3

E1002      Name1002_1

 

3. 通过查找研究得出以下解决方案

3.1 创建一个列合并函数：

create function f_merge(@id int) 
returns varchar(8000) 
as

 
begin

 

declare @str varchar(8000) 
set @str='' 
select @str=@str+','+rtrim(name) from table_name where id=@id 
set @str=right(@str,len(@str)-1) 
return(@str)

 

end

 

3.2 使用此函数得出结果：

select Entity_No,dbo.f_merge(Entity_No) Name from table_name group by Entity_No

在DB2中用递归的方式实现：
WITH B (FATHER,SON,ID,ALLINFO) AS 
(SELECT RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID)) AS FATHER, 
RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID) + 1) AS SON, 
A.ID, CAST(A.INFO AS VARCHAR(100)) 
FROM TKEAMS.T_DETAIL A 
UNION ALL 
SELECT C.FATHER,C.SON,C.ID, 
RTRIM(LTRIM(B.ALLINFO))||','||RTRIM(LTRIM(CHAR(C.INFO))) 
FROM (SELECT 
RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID)) AS FATHER, 
RTRIM(LTRIM(CHAR(A.ID)))|| CHAR(ROW_NUMBER() OVER(PARTITION BY ID) + 1) AS SON, 
A.ID, A.INFO FROM TKEAMS.T_DETAIL A) AS C, B 
WHERE B.SON= C.FATHER) 
SELECT E.ID,E.NAME,E.AGE,D.ALLINFO 
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LENGTH(ALLINFO) DESC) AS ROW_NUM, B.ID,B.ALLINFO FROM B) AS D,T_BASE E 
WHERE D.ROW_NUM = 1 AND D.ID=E.ID;