Several implementation methods for a typical row/column conversion in Oracle)

Assume that, as shown in the following table, the number of rows corresponding to each I value is not fixed.

SQL> select * from T;

I a d
---------------------------------------
1 B 10:55:42
1 A 2008-03-27 10:55:46
1 D 10:55:30
2 Z 2008-03-27 10:55:55
2 t 10:55:59

To obtain the following results, note that the string must be sorted by the time of column D:

1 d, B,
2 Z, T

This is a typical column-and-column conversion. There are several implementation methods.

1. UDF implementation

Create or replace function my_concat (N number)
Return varchar2
Is
Type typ_cursor is ref cursor;
V_cursor typ_cursor;
V_temp varchar2 (10 );
V_result varchar2 (4000): = '';
V_ SQL varchar2 (200 );
Begin
V_ SQL: = 'select A from t where I = '| n | 'order by d ';
Open v_cursor for v_ SQL;
Loop
Fetch v_cursor into v_temp;
Exit when v_cursor % notfound;
V_result: = v_result | ',' | v_temp;
End loop;
Return substr (v_result, 2 );
End;

SQL> select I, my_concat (I) from T group by I;

I my_concat (I)
------------------------------
1 d, B,
2 Z, T

Although this method can meet the requirements, if the table t has a large amount of data and a large number of I values, a SELECT statement must be executed for each I value, the number of scans and sorting times is proportional to the I value, and the performance is very poor.

2. Use sys_connect_by_path

Select I, ltrim (max (sys_connect_by_path (A, ','), ',')
From
(
Select I, A, D, min (d) over (partition by I) d_min,
(Row_number () over (order by I, d) + (dense_rank () over (order by I) numid
From t
)
Start with D = d_min connect by numid-1 = prior numid
Group by I;

From the execution plan, this method only needs to scan the table twice, which is much more efficient than the custom function method, especially when the table data volume is large:

3. Use wm_sys.wm_concat

This function can also achieve similar column-and-column conversion requirements, but it seems that there is no way to directly sort by another column, so you need to sort the order through subqueries or temporary tables first

SQL> select I, wmsys. wm_concat (a) from T group by I;

I wmsys. wm_concat ()
------------------------------
1 B, A, D
2 Z, T

SQL> select I, wmsys. wm_concat ()
2 from
3 (select * from t order by I, d)
4 group by I;

I wmsys. wm_concat ()
------------------------------
1 d, B,
2 Z, T

In the execution plan, you only need to perform a table scan. However, this function is encrypted and the execution plan does not display internal operations of the function.