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语句可以得到想要的结果：

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

转载于:https://www.cnblogs.com/dolphin0714/archive/2009/12/28/1634518.html