ORACLE-merge query results

ORACLE-merge query results

Recently, I encountered a problem. I need to combine the values of a certain field in the query results to show that I had a workund und through my own efforts and wrote it down for forgetting.

The following is the form of data in the database. It is queried using common statements as follows:

Select Iq_No, Report_No from Iq_Body

 

Query results:

Iq_No Report_no
--------------------------------------------------
N3200000109042000002 320410109081597
N3200000109042000002 320410109081598
N3200000109042000002 320410109081599
N3200000109042000003 320410109081600
N3200000109042000003 320410109081124

 

Expected results:

Iq_No Report_No
--------------------------------------------
N3200000109042000002 3200000109081597,3200000109081598, 320410109081599
N3200000109042000003 3200000109081600,3200000109081124

 

Use the following SQL statement to obtain the expected result:

SQL statement

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