Several implementation methods of Oracle row-column Conversion

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.

----- Column and column conversion 1
Data Format 1
Card_code Q bal
-----------------------------
001 1 27
001 2 10
001 3 36
001 4 97
002 1 96
002 2 12
002 3 15
002 4 32

Data Format 2
Card_code q1 q2 Q3 Q4
-------------------------------------------------
001 27 10 36 97
002 96 12 15 32

-- Format 1 To Format 2

Select. card_code, sum (decode (. q, 1,. bal, 0) Q1, sum (decode (. q, 2,. bal, 0) Q2,
Sum (decode (A. Q, 3, A. Bal, 0) Q3, sum (decode (A. Q, 4, A. Bal, 0) Q4
From my_card
Group by A. card_code
Order by 1;
 
-- Format 2 to format 1
Select T. card_code, T. rn Q, decode (T. RN, 1, T. q1, 2, T. q2, 3, T. q3, 4, T. q4) BAL
From (select a. *, B. Rn
From my_card_two,
(Select rownum Rn
From dual
Connect by rownum <= 4) B) T
Order by 1, 2;
 
------ Row and column conversion 2
Data Format 1
Card_code Q
---------------------------------------------------------
001 quarter_1
001 quarter_2
001 quarter_3
001 quarter_4
002 quarter_1
002 quarter_2
002 quarter_3
002 quarter_4

Data Format 2
Card_code Q
--------------------------------------
002 quarter_1; quarter_2; quarter_3; quarter_4
001 quarter_1; quarter_2; quarter_3; quarter_4

-- Format 1 To Format 2
Select t1.card _ code, substr (max (sys_connect_by_path (t1.q, ';'), 2) q
From (select a. card_code, A. Q,
Row_number () over (partition by A. card_code order by A. Q) Rn
From my_card_t3 A) T1
Start with t1.rn = 1
Connect by t1.card _ code = prior t1.card _ code
And t1.rn-1 = prior t1.rn
Group by t1.card _ code;
 
-- Format 2 to format 1
Select T. card_code,
Substr (T. Q,
Instr (';' | T. Q, ';', 1, RN ),
Instr (T. q | ';', ';', 1, RN)-instr (';' | T. q, ';', 1, RN) q
From (select a. card_code, A. Q, B. Rn
From my_card_t4,
(Select rownum Rn
From dual
Connect by rownum <= 100) B
Where instr (';' | A. Q, ';', 1, RN)> 0) T
Order by 1, 2;