是否可以在DB2中，用单句SQL实现这个查询
是否可以在DB2中，用单句SQL实现下面的查询：有一个表T，内容如下：
ID A B
1 1 a
2 2 b
3 1 c
4 1 d
5 3 e
6 3 f
要求检索进行信息组合，查询结果如下：
RA RB
1 a,c,d
2 b
3 e,f
SQL应该如何写？ WITH
T1(A,B,NUM) AS
( SELECT A,B,ROW_NUMBER() OVER(PARTITION BY A ORDER BY ID)
FROM T ),
T2(RA,RB,NUM) AS
( SELECT A,CHAR(B,10),NUM FROM T1 WHERE NUM = 1
UNION ALL
SELECT T2.RA,RTRIM(T2.RB)||','||T1.B,T1.NUM FROM T1 , T2
WHERE T1.NUM = T2.NUM + 1
AND T1.A = T2.RA )
SELECT RA,RB FROM T2
WHERE NUM = ( SELECT MAX(NUM) FROM T2 TEMP WHERE TEMP.RA = T2.RA)
ORDER BY RA
; 原帖由 Germin 于 2009-5-17 19:26 发表 http://bbs2.chinaunix.net/images/common/back.gif
WITH
T1(A,B,NUM) AS
( SELECT A,B,ROW_NUMBER() OVER(PARTITION BY A ORDER BY ID)
FROM T ),
T2(RA,RB,NUM) AS
( SELECT A,CHAR(B,10),NUM FROM T1 WHERE NUM = 1
UNION ALL
SELECT T2.RA,RT ...
真厉害，我从没敢想象用recursion。
传统的是用XML，用下面的例子稍稍改改就可以了。
http://it.toolbox.com/blogs/db2luw/pivot-query-12757
结果：
Id Phone numbers
1001 9495000,9834500,39238200
SELECT EMP_ID,
SUBSTR(Phone_nums, 1, LENGTH(Phone_nums) -1)
FROM
(SELECT Emp_id,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(Name "A", phone))
AS VARCHAR(90)),
'< A>', ''),
'</A>', ',') AS Phone_nums
FROM Emp_Phones
GROUP BY EMP_ID) AS X;
页:
[1]