ORACLE-合并查询结果

最近遇到了一个问题，需要将查询出来的结果中某一字段的值合并起来显示，通过自己的努力有了个Workaround，记下来以备后忘。

下面是数据库中的数据存在形式，用普通的语句查询如下

Select Iq_No,Report_No from Iq_Body

 

查询出的结果：

Iq_No                        Report_no
--------------------------------------------------
N3204101090421000002          320410109081597
N3204101090421000002          320410109081598
N3204101090421000002          320410109081599
N3204101090421000003          320410109081600
N3204101090421000003          320410109081124

 

实际想要的结果：

Iq_No                    Report_No
--------------------------------------------
N3204101090421000002     320410109081597,320410109081598,320410109081599
N3204101090421000003     320410109081600,320410109081124

 

用下面的SQL语句可以得到想要的结果：

 1 SELECT IQ_NO,
 2        REPLACE(SUBSTR(MAX(CASE
 3                             WHEN REPORT_NO IS NOT NULL OR LENGTH(REPORT_NO) = 0 THEN
 4                              SYS_CONNECT_BY_PATH(REPORT_NO, ';')
 5                           END),
 6                       2),
 7                ';',
 8                ',') REPORT_NO
 9   FROM (SELECT IQ_NO,
10                REPORT_NO,
11                rn,
12                LEAD(rn) OVER(PARTITION BY IQ_NO ORDER BY rn) rn1
13           FROM (SELECT IQ_NO,
14                        REPORT_NO,
15                        ROW_NUMBER() OVER(ORDER BY REPORT_NO DESC) rn
16                   FROM IQ_BODY))
17  START WITH rn1 IS NULL
18 CONNECT BY rn1 = PRIOR rn
19  GROUP BY IQ_NO
20 

 

                                                                                                                                                         Agoni  2009-12-28