Oracle multi-row Merge row method

If there is a table below, where the number of rows for each I value is variable

SQL code

2. Sql> SELECT * from t;
6. I A D
8. ---------- ---------- -------------------
10. 1 b 2008-03-27 10:55:42
12. 1 a 2008-03-27 10:55:46
14. 1 D 2008-03-27 10:55:30
16. 2 Z 2008-03-27 10:55:55
18. 2 T 2008-03-27 10:55:59
22. ---to get the following result, note that the string needs to be sorted by the time of column D:
26. 1 d,b,a
28. 2 z,t

This is a more typical row and column conversion, there are several ways to implement

1. Custom Function Implementations

SQL code

2. Create or replace function My_concat (n number)
4. return VARCHAR2
6. Is
8. Type typ_cursor is ref cursor;
10. V_cursor Typ_cursor;
12. V_temp VARCHAR2 (10);
14. V_result varchar2 (4000): = ";
16. V_sql VARCHAR2 (200);
18. Begin
20. V_sql: = ' Select a from t where i= ' | | | n | |  ' ORDER by d ';
22. Open v_cursor for v_sql;
24. Loop
26. fetch v_cursor into v_temp;
28. Exit when V_cursor%notfound;
30. V_result: = V_result | |  ', ' | | | v_temp;
32. end Loop;
34. return substr (v_result,2);
36. End
40. sql> Select I,my_concat (i) from the T Group by I;
44. I my_concat (i)
46. ---------- --------------------
48. 1 d,b,a
50. 2 z,t

Although this approach can achieve the requirements, but if the data volume of table t is large, I the value of a lot of cases, because for each I value to execute a SELECT, scan and sort the number of times and the value of I is proportional to the performance will be very poor.

2. Using Sys_connect_by_path

SQL code

2. Select I,ltrim (max (Sys_connect_by_path (A,', ')),', ') a
4. From
6. (
8. Select I,a,d,min (d) over (partition by i) d_min,
10. (Row_number () over (order by I,d)) + (Dense_rank () over (order by i)) numid
12. From T
14. )
16. Start with d=d_min connect by numid-1=prior numid
18. Group by I;

From the execution plan, this method only needs to scan two tables, more efficient than the method of custom functions, especially when the data volume in the table is large:

3. Using Wm_sys.wm_concat
This function can also implement a similar row-and-column conversion requirement, but there seems to be no way to sort directly according to the other columns, so you need to first order through a subquery or temporary table:

SQL code

2. sql> Select I,wmsys.wm_concat (a) from T Group by I;
6. I Wmsys. Wm_concat (A)
8. ---------- --------------------
10. 1 b,a,d
12. 2 z,t
16. sql> Select I,wmsys.wm_concat (a)
18. 2 from
20. 3 (select * from t order by I,d)
22. 4 GROUP by I;
26. I Wmsys. Wm_concat (A)
28. ---------- --------------------
30. 1 d,b,a
32. 2 z,t

On the execution plan, only one table scan is required, but the function is encrypted, and the execution plan does not show the action inside the function.