Question

DB2，实现查询结果中多行字段合并到一行上的SQL语句写法 ？

CREATE TABLE temp
(
DEPTNO int,
ENAME varchar(20),
SEX varchar(20),
AGE varchar(20)
);
insert into temp values(10,'aaa','F','18');
insert into temp values(10,'bbb','F','19');
insert into temp values(10,'ccc','F','20');
insert into temp values(20,'ddd','M','21');
insert into temp values(20,'eee','M','22');
insert into temp values(20,'fff','M','23');
insert into temp values(30,'hhh','X','24');
insert into temp values(30,'ggg','X','25');
insert into temp values(30,'yyy','X','26');
insert into temp values(30,'ttt','X','27');

要求结果如下：
10|F| aaa bbb ccc
20|M| ddd eee fff
30|X| ggg hhh ttt yyy

用Oracle的语句在 Toad中运行DB2 数
据库！ 老报错！求修改！或者其他的实现SQL语句？ 多谢！
SELECT deptno,
SYS_CONNECT_BY_PATH (ename, ' ') AS ename,
sex,
SYS_CONNECT_BY_PATH (age, ' ') AS age
FROM (SELECT deptno,
ename,
sex,
age,
RANK () OVER (ORDER BY deptno)
+ ROW_NUMBER () OVER (ORDER BY deptno)
rn,
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY deptno) rm
FROM temp) a1
WHERE a1.ROWID IN (SELECT MAX (a2.ROWID)
FROM temp a2
WHERE a2.deptno = a1.deptno)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1
昨天试了一下，还是用递归来做！
WITH Recursive_Test_Par(deptNo,ename,sex,age,Rk_Num) as(
SELECT deptNo,ename,sex,age,ROW_NUMBER() OVER(PARTITION BY deptno) --分组，生成序列，自我关联之用
FROM temp
),
Ename_Join(deptNo,ename,sex,age,R_Num) as(
SELECT deptno,CAST(ename AS VARCHAR(100)),sex,age,Rk_Num from Recursive_Test_Par WHERE Rk_Num=1
UNION ALL
SELECT a1.deptNo,CAST(a1.ename||'#'||b1.ename AS VARCHAR(100)),a1.sex,a1.age,a1.R_Num+1
from Ename_Join a1,Recursive_Test_Par b1
WHERE a1.deptNo=b1.deptNo and a1.R_Num=b1.Rk_Num-1
)
SELECT a.deptNo,a.ename,a.sex,a.age FROM Ename_Join a INNER JOIN
(SELECT deptNo,max(R_Num) R_Num from Ename_Join
GROUP BY deptNo) b
ON a.deptNo=b.deptNo and a.R_Num=b.R_Num;
结果如下：

没有报错！





Answer 1

WITH
t1 (id, DEPTNO, SEX, ENAME) AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY DEPTNO, SEX ORDER BY ENAME) AS ID,
DEPTNO,
SEX,
ENAME
FROM
temp
),
t2 (id, DEPTNO, SEX, ENAME) AS
(
SELECT
t1.id,
t1.DEPTNO,
t1.SEX,
CAST(t1.ENAME AS varchar(100)) AS ENAME
FROM
t1
WHERE
t1.id = 1
UNION ALL
SELECT
t1.id,
t1.DEPTNO,
t1.SEX,
CAST( t2.ENAME || ' ' || t1.ENAME AS varchar(100)) AS ENAME
FROM
t1, t2
WHERE
t1.DEPTNO = t2.DEPTNO
AND t1.SEX = t2.SEX
AND t1.id = (t2.id + 1)
)
SELECT
DEPTNO,
SEX,
ENAME
FROM
t2
WHERE
NOT EXISTS (
SELECT 1
FROM t2 t22
WHERE
t2.DEPTNO = t22.DEPTNO
AND t2.SEX = t22.SEX
AND t2.id < t22.id
);

DEPTNO SEX ENAME
----------- -------------------- -----------------------------------------------
-----------------------------------------------------
SQL0347W 递归公共表表达式 "TEST.T2" 可能包含无限循环。 SQLSTATE=01605

10 F aaa bbb ccc
20 M ddd eee fff
30 X ggg hhh ttt yyy
已选择 3 条记录，打印 1 条警告消息。
db2 =>