Summary of oracle row-column Conversion

Most of the questions raised in the Forum recently are related to column and column conversion. Therefore, I have summarized the knowledge of column and column conversion,
I hope it will be helpful to you and make some mistakes at the same time. Please point out,
I am also studying in the writing process. Let's take a look at it together.

Column and column conversion includes the following six situations:
* Column-to-row
* Row-to-column Conversion
* Convert multiple columns into strings
* Convert multiple rows into strings
* String Conversion into multiple columns
* Convert a string to multiple rows.

The following are examples.

First, declare that some examples require 10 Gbit/s or later knowledge:
A. Master the model clause,
B. Regular Expression
C. Enhanced hierarchical Query

The scope of the discussion only applies to 8i, 9i, 10g and later versions. Begin:

1. Column-to-row
Create table t_col_row (
Id int,
C1 VARCHAR2 (10 ),
C2 VARCHAR2 (10 ),
C3 VARCHAR2 (10 ));

Insert into t_col_row VALUES (1, 'v11', 'v21', 'v31 ');
Insert into t_col_row VALUES (2, 'v12', 'v22', NULL );
Insert into t_col_row VALUES (3, 'v13', NULL, 'v33 ');
Insert into t_col_row VALUES (4, NULL, 'v24', 'v34 ');
Insert into t_col_row VALUES (5, 'v15', NULL, NULL );
Insert into t_col_row VALUES (6, NULL, NULL, 'v35 ');
Insert into t_col_row VALUES (7, NULL );
COMMIT;

SELECT * FROM t_col_row;

1) UNION ALL
Applicability: 8i, 9i, 10g and later
SELECT id, 'c1 'cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2 'cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

If empty rows do not need to be converted, you only need to add a where condition,
Where column is not null.

2) MODEL
Applicability: 10 Gb or later
SELECT id, cn, cv FROM t_col_row
MODEL
RETURN UPDATED ROWS
Partition by (ID)
Dimension by (0 AS n)
MEASURES ('xx' AS cn, 'yyy' AS cv, c1, c2, c3)
RULES UPSERT ALL
(
Cn [1] = 'c1 ',
Cn [2] = 'c2 ',
Cn [3] = 'c3 ',
Cv [1] = c1 [0],
Cv [2] = c2 [0],
Cv [3] = c3 [0]
)
Order by id, cn;

3) collection
Applicability: 8i, 9i, 10g and later
To create an object and a set:
Create type cv_pair as object (cn VARCHAR2 (10), cv VARCHAR2 (10 ));

Create type cv_varr as varray (8) OF cv_pair;

SELECT id, t.cn AS cn, t. cv AS cv
FROM t_col_row,
TABLE (cv_varr (cv_pair ('c1', t_col_ro1_c1 ),
Cv_pair ('c2 ', t_col_row.c2 ),
Cv_pair ('c3', t_col_row.c3) t
Order by 1, 2;

2. Row-to-column Conversion

Create table t_row_col
SELECT id, 'c1 'cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2 'cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

SELECT * FROM t_row_col order by 1, 2;

1) AGGREGATE FUNCTION
Applicability: 8i, 9i, 10g and later
SELECT id,
MAX (decode (cn, 'c1', cv, NULL) AS c1,
MAX (decode (cn, 'c2 ', cv, NULL) AS c2,
MAX (decode (cn, 'c3', cv, NULL) AS c3
FROM t_row_col
Group by id
Order by 1;

The MAX aggregate function can also be replaced by sum, min, avg, and other Aggregate functions.

The specified transpose column can only have one column, but the fixed column can have multiple columns. See the following example:

SELECT mgr, deptno, empno, ename FROM emp order by 1, 2;

SELECT mgr,
Deptno,
MAX (decode (empno, '20140901', ename, NULL) "7788 ",
MAX (decode (empno, '20140901', ename, NULL) "7902 ",
MAX (decode (empno, '20140901', ename, NULL) "7844 ",
MAX (decode (empno, '20140901', ename, NULL) "7521 ",
MAX (decode (empno, '20140901', ename, NULL) "7900 ",
MAX (decode (empno, '20140901', ename, NULL) "7499 ",
MAX (decode (empno, '20140901', ename, NULL) "7654"
FROM emp
WHERE mgr IN (7566,769 8)
AND deptno IN (20, 30)
Group by mgr, deptno
Order by 1, 2;

Here, the transposed column is empno, and the fixed column is mgr and deptno.

Another way to convert rows to columns is to change the row values in the same group to a single column value, but the transposed row values are not changed to column names:

ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3
1 c1 v11 c2 v21 c3 v31
2 c1 v12 c2 v22 c3
3 c1 v13 c2 c3 v33
4 c1 c2 v24 c3 v34
5 c1 v15 c2 c3
6 c1 c2 c3 v35
7 c1 c2 c3

In this case, you can use the analysis function:

SELECT id,
MAX (decode (rn, 1, cn, NULL) cn_1,
MAX (decode (rn, 1, cv, NULL) cv_1,
MAX (decode (rn, 2, cn, NULL) cn_2,
MAX (decode (rn, 2, cv, NULL) cv_2,
MAX (decode (rn, 3, cn, NULL) cn_3,
MAX (decode (rn, 3, cv, NULL) cv_3
FROM (SELECT id,
Cn,
Cv,
Row_number () over (partition by id order by cn, cv) rn
FROM t_row_col)
Group by id;

2) PL/SQL
Applicability: 8i, 9i, 10g and later
This can be used when the row value is not fixed.
The following is a package I wrote.
P_rows_column_real is used to convert the first unlimited column mentioned above;
P_rows_column is used to convert the second unlimited column.

Create or replace package pkg_dynamic_rows_column
TYPE refc is ref cursor;

PROCEDURE p_print_ SQL (p_txt VARCHAR2 );

FUNCTION f_split_str (p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2;

PROCEDURE p_rows_column (p_table IN VARCHAR2,
P_keep_cols IN VARCHAR2,
P_effect_cols IN VARCHAR2,
P_where IN VARCHAR2 default null,
P_refc in out refc );

PROCEDURE p_rows_column_real (p_table IN VARCHAR2,
P_keep_cols IN VARCHAR2,
P_effect_col IN VARCHAR2,
P_1_t_val IN VARCHAR2,
P_where IN VARCHAR2 default null,
P_refc in out refc );
END;
/
Create or replace package body pkg_dynamic_rows_column

PROCEDURE p_print_ SQL (p_txt VARCHAR2) IS
V_len INT;
BEGIN
V_len: = length (p_txt );
FOR I IN 1 .. v_len/250 + 1 LOOP
Dbms_output.put_line (substrb (p_txt, (I-1) * 250 + 1,250 ));
End loop;
END;

FUNCTION f_split_str (p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2 IS
V_first INT;
V_last INT;
BEGIN
IF p_seq <1 THEN
Return null;
End if;
IF p_seq = 1 THEN
IF instr (p_str, p_division, 1, p_seq) = 0 THEN
RETURN p_str;
ELSE
RETURN substr (p_str, 1, instr (p_str, p_division, 1)-1 );
End if;
ELSE
V_first: = instr (p_str, p_division, 1, p_seq-1 );
V_last: = instr (p_str, p_division, 1, p_seq );
IF (v_last = 0) THEN
IF (v_first> 0) THEN
RETURN substr (p_str, v_first + 1 );
ELSE
Return null;
End if;
ELSE
RETURN substr (p_str, v_first + 1, v_last-v_first-1 );
End if;
End if;
END f_split_str;

PROCEDURE p_rows_column (p_table IN VARCHAR2,
P_keep_cols IN VARCHAR2,
P_effect_cols IN VARCHAR2,
P_where IN VARCHAR2 default null,
P_refc in out refc) IS
V_ SQL VARCHAR2 (4000 );
TYPE v_keep_ind_by is table of VARCHAR2 (4000) index by BINARY_INTEGER;
V_keep v_keep_ind_by;

TYPE v_effect_ind_by is table of VARCHAR2 (4000) index by BINARY_INTEGER;
V_1_v_1_t_ind_by;

V_keep_cnt INT;
V_effect_cnt INT;
V_max_cols INT;
V_partition VARCHAR2 (4000 );
V_partition1 VARCHAR2 (4000 );
V_partition2 VARCHAR2 (4000 );
BEGIN
V_keep_cnt: = length (p_keep_cols)-length (REPLACE (p_keep_cols, ',') + 1;
V_1_t_cnt: = length (p_1_t_cols )-
Length (REPLACE (p_1_t_cols, ',') + 1;
FOR I IN 1 .. v_keep_cnt LOOP
V_keep (I): = f_split_str (p_keep_cols, ',', I );
End loop;
FOR j IN 1 .. v_rjt_cnt LOOP
V_1_( j): = f_split_str (p_1_t_cols, ',', j );
End loop;
V_ SQL: = 'select max (count (*) from '| p_table | 'group ';
FOR I IN 1 .. v_keep.LAST LOOP
V_ SQL: = v_ SQL | v_keep (I) | ',';
End loop;
V_ SQL: = rtrim (v_ SQL ,',');
Execute immediate v_ SQL
INTO v_max_cols;
V_partition: = 'select ';
FOR x IN 1 .. v_keep.COUNT LOOP
V_partition1: = v_partition1 | v_keep (x) | ',';
End loop;
FOR y IN 1 .. v_20.t.count LOOP
V_partition2: = v_partition2 | v_1_( y) | ',';
End loop;
V_partition1: = rtrim (v_partition1 ,',');
V_partition2: = rtrim (v_partition2 ,',');
V_partition: = v_partition | v_partition1 | ',' | v_partition2 |
', Row_number () over (partition by' | v_partition1 |
'ORDER BY' | v_partition2 | ') rn from' | p_table;
V_partition: = rtrim (v_partition ,',');
V_ SQL: = 'select ';
FOR I IN 1 .. v_keep.COUNT LOOP
V_ SQL: = v_ SQL | v_keep (I) | ',';
End loop;
FOR I IN 1 .. v_max_cols LOOP
FOR j IN 1 .. v_20.t.count LOOP
V_ SQL: = v_ SQL | 'max (decode (rn, '| I |', '| v_1_( j) |
', Null)' | v_1_( j) | '_' | I | ',';
End loop;
End loop;
IF p_where IS NOT NULL THEN
V_ SQL: = rtrim (v_ SQL, ',') | 'from ('| v_partition | ''|
P_where | ') group ';
ELSE
V_ SQL: = rtrim (v_ SQL, ',') | 'from ('| v_partition |
') Group ';
End if;
FOR I IN 1 .. v_keep.COUNT LOOP
V_ SQL: = v_ SQL | v_keep (I) | ',';
End loop;
V_ SQL: = rtrim (v_ SQL ,',');
P_print_ SQL (v_ SQL );
OPEN p_refc FOR v_ SQL;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc
SELECT 'x' FROM dual WHERE 0 = 1;
END;

PROCEDURE p_rows_column_real (p_table IN VARCHAR2,
P_keep_cols IN VARCHAR2,
P_effect_col IN VARCHAR2,
P_1_t_val IN VARCHAR2,
P_where IN VARCHAR2 default null,
P_refc in out refc) IS
V_ SQL VARCHAR2 (4000 );
TYPE v_keep_ind_by is table of VARCHAR2 (4000) index by BINARY_INTEGER;
V_keep v_keep_ind_by;
TYPE v_effect_ind_by is table of VARCHAR2 (4000) index by BINARY_INTEGER;
V_1_v_1_t_ind_by;
V_keep_cnt INT;
V_group_by VARCHAR2 (2000 );
BEGIN
V_keep_cnt: = length (p_keep_cols)-length (REPLACE (p_keep_cols, ',') + 1;
FOR I IN 1 .. v_keep_cnt LOOP
V_keep (I): = f_split_str (p_keep_cols, ',', I );
End loop;
V_ SQL: = 'select' | 'Cast ('| p_effect_col |
'As varchar2 (200) as '| p_0000t_col | 'from' | p_table |
'Group by' | p_effect_col;
Execute immediate v_ SQL BULK COLLECT
INTO v_1;
FOR I IN 1 .. v_keep.COUNT LOOP
V_group_by: = v_group_by | v_keep (I) | ',';
End loop;
V_group_by: = rtrim (v_group_by ,',');
V_ SQL: = 'select' | v_group_by | ',';

FOR x IN 1 .. v_20.t.count LOOP
V_ SQL: = v_ SQL | 'max (decode ('| p_effect_col |', '| chr (39) |
V_1_( x) | chr (39) | ',' | p_1_t_val |
', Null) as "' | v_1_( x) | '",';
End loop;
V_ SQL: = rtrim (v_ SQL ,',');
IF p_where IS NOT NULL THEN
V_ SQL: = v_ SQL | 'from' | p_table | p_where | 'group by' |
V_group_by;
ELSE
V_ SQL: = v_ SQL | 'from' | p_table | 'group by' | v_group_by;
End if;
P_print_ SQL (v_ SQL );
OPEN p_refc FOR v_ SQL;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc
SELECT 'x' FROM dual WHERE 0 = 1;
END;

END;
/

3. Convert multiple columns into strings
Create table t_col_str
SELECT * FROM t_col_row;

This is relatively simple and can be implemented using the | or concat function:
SELECT concat ('A', 'B') FROM dual;

1) | OR concat
Applicability: 8i, 9i, 10g and later
SELECT * FROM t_col_str;

Select id, c1 | ',' | c2 | ',' | c3 AS c123
FROM t_col_str;

4. Convert multiple rows into strings
Create table t_row_str (
Id int,
Col VARCHAR2 (10 ));

Insert into t_row_str VALUES (1, 'A ');
Insert into t_row_str VALUES (1, 'B ');
Insert into t_row_str VALUES (1, 'C ');
Insert into t_row_str VALUES (2, 'A ');
Insert into t_row_str VALUES (2, 'D ');
Insert into t_row_str VALUES (2, 'E ');
Insert into t_row_str VALUES (3, 'C ');
COMMIT;

SELECT * FROM t_row_str;

1) MAX + decode
Applicability: 8i, 9i, 10g and later
SELECT id,
MAX (decode (rn, 1, col, NULL) |
MAX (decode (rn, 2, ',' | col, NULL) |
MAX (decode (rn, 3, ',' | col, NULL) str
FROM (SELECT id,
Col,
Row_number () over (partition by id order by col) AS rn
FROM t_row_str) t
Group by id
Order by 1;

2) row_number + lead
Applicability: 8i, 9i, 10g and later
SELECT id, str
FROM (SELECT id,
Row_number () over (partition by id order by col) AS rn,
Col | lead (',' | col, 1) over (partition by id order by col) |
Lead (',' | col, 2) over (partition by id order by col) |
Lead (',' | col, 3) over (partition by id order by col) AS str
FROM t_row_str)
WHERE rn = 1
Order by 1;

3) MODEL
Applicability: 10 Gb or later
SELECT id, substr (str, 2) str FROM t_row_str
MODEL
RETURN UPDATED ROWS
Partition by (ID)
Dimension by (row_number () over (partition by id order by col) AS rn)
MEASURES (CAST (col AS VARCHAR2 (20) AS str)
RULES UPSERT
ITERATE (3) UNTIL (presentv (str [iteration_number + 2], 1, 0) = 0)
(Str [0] = str [0] | ',' | str [iteration_number + 1])
Order by 1;

4) sys_connect_by_path
Applicability: 8i, 9i, 10g and later
SELECT t. id, MAX (substr (sys_connect_by_path (t. col, ','), 2) str
FROM (SELECT id, col, row_number () over (partition by id order by col) rn
FROM t_row_str) t
Start with rn = 1
Connect by rn = PRIOR rn + 1
AND id = PRIOR id
Group by t. id;

Applicability: 10 Gb or later
SELECT t. id, substr (sys_connect_by_path (t. col, ','), 2) str
FROM (SELECT id, col, row_number () over (partition by id order by col) rn
FROM t_row_str) t
WHERE connect_by_isleaf = 1
Start with rn = 1
Connect by rn = PRIOR rn + 1
AND id = PRIOR id;

5) wmsys. wm_concat
Applicability: 10 Gb or later
This function is pre-defined to separate strings by ','. If you want to use other symbols to separate strings, replace.

SELECT id, REPLACE (wmsys. wm_concat (col ),',','/')
FROM t_row_str
Group by id;

5. convert a string into multiple columns
It is actually a string splitting problem.

Create table t_str_col
Select id, c1 | ',' | c2 | ',' | c3 AS c123
FROM t_col_str;

SELECT * FROM t_str_col;

1) substr + instr
Applicability: 8i, 9i, 10g and later
SELECT id,
C123,
Substr (c123, 1, instr (c123 | ', 1, 1)-1) c1,
Substr (c123,
Instr (c123 | ', 1, 1) + 1,
Instr (c123 | ', 1, 2)-instr (c123 |', ', 1, 1)-1) c2,
Substr (c123,
Instr (c123 | ', 1, 2) + 1,
Instr (c123 | ', 1, 3)-instr (c123 |', ', 1, 2)-1) c3
FROM t_str_col
Order by 1;

2) regexp_substr
Applicability: 10 Gb or later
SELECT id,
C123,
Rtrim (regexp_substr (c123 | ',','.*? '|', ', 1, 1),', ') AS c1,
Rtrim (regexp_substr (c123 | ',','.*? '|', ', 1, 2),', ') AS c2,
Rtrim (regexp_substr (c123 | ',','.*? '|', ', 1, 3),', ') AS c3
FROM t_str_col
Order by 1;

6. convert a string to multiple rows
Create table t_str_row
SELECT id,
MAX (decode (rn, 1, col, NULL) |
MAX (decode (rn, 2, ',' | col, NULL) |
MAX (decode (rn, 3, ',' | col, NULL) str
FROM (SELECT id,
Col,
Row_number () over (partition by id order by col) AS rn
FROM t_row_str) t
Group by id
Order by 1;

SELECT * FROM t_str_row;

1) UNION ALL
Applicability: 8i, 9i, 10g and later
SELECT id, 1 AS p, substr (str, 1, instr (str | ',', 1, 1)-1) AS cv
FROM t_str_row
UNION ALL
SELECT id,
2 AS p,
Substr (str,
Instr (str | ', 1, 1) + 1,
Instr (str | ', 1, 2)-instr (str |', ', 1, 1)-1) AS cv
FROM t_str_row
UNION ALL
SELECT id,
3 AS p,
Substr (str,
Instr (str | ', 1, 1) + 1,
Instr (str | ', 1, 2)-instr (str |', ', 1, 1)-1) AS cv
FROM t_str_row
Order by 1, 2;

Applicability: 10 Gb or later
SELECT id, 1 AS p, rtrim (regexp_substr (str | ',','.*? '|', ', 1, 1),', ') AS cv
FROM t_str_row
UNION ALL
SELECT id, 2 AS p, rtrim (regexp_substr (str | ',','.*? '|', ', 1, 2),', ') AS cv
FROM t_str_row
UNION ALL
SELECT id, 3 AS p, rtrim (regexp_substr (str | ',','.*? '|', ', 1, 3),', ') AS cv
FROM t_str_row
Order by 1, 2;

2) VARRAY
Applicability: 8i, 9i, 10g and later
To create a variable array:
Create or replace type ins_seq_type is varray (8) of number;

SELECT * from table (ins_seq_type (1, 2, 3, 4, 5 ));

SELECT t. id,
C. column_value AS p,
Substr (t. ca,
Instr (t. ca, ',', 1, c. column_value) + 1,
Instr (t. ca, ',', 1, c. column_value + 1 )-
(Instr (t. ca, ',', 1, c. column_value) + 1) AS cv
FROM (SELECT id,
',' | Str | ',' AS ca,
Length (str | ',')-nvl (length (REPLACE (str, ','), 0) AS cnt
FROM t_str_row) t
Inner join table (ins_seq_type (1, 2, 3) c ON c. column_value <=
T. cnt
Order by 1, 2;

3) SEQUENCE series
This method is mainly used to generate a continuous integer column. There are many methods to generate a continuous integer column, mainly including:
Connect by, ROWNUM + all_objects, CUBE, etc.
Applicability: 8i, 9i, 10g and later
SELECT t. id,
C. lv AS p,
Substr (t. ca,
Instr (t. ca, ',', 1, c. lv) + 1,
Instr (t. ca, ',', 1, c. lv + 1 )-
(Instr (t. ca, ',', 1, c. lv) + 1) AS cv
FROM (SELECT id,
',' | Str | ',' AS ca,
Length (str | ',')-nvl (length (REPLACE (str, ','), 0) AS cnt
FROM t_str_row) t,
(Select level lv FROM dual connect by level <= 5) c
WHERE c. lv <= t. cnt
Order by 1, 2;

SELECT t. id,
C. rn AS p,
Substr (t. ca,
Instr (t. ca, ',', 1, c. rn) + 1,
Instr (t. ca, ',', 1, c. rn + 1 )-
(Instr (t. ca, ',', 1, c. rn) + 1) AS cv
FROM (SELECT id,
',' | Str | ',' AS ca,
Length (str | ',')-nvl (length (REPLACE (str, ','), 0) AS cnt
FROM t_str_row) t,
(SELECT rownum rn FROM all_objects WHERE rownum <= 5) c
WHERE c. rn <= t. cnt
Order by 1, 2;

SELECT t. id,
C. cb AS p,
Substr (t. ca,
Instr (t. ca, ',', 1, c. cb) + 1,
Instr (t. ca, ',', 1, c. cb + 1 )-
(Instr (t. ca, ',', 1, c. cb) + 1) AS cv
FROM (SELECT id,
',' | Str | ',' AS ca,
Length (str | ',')-nvl (length (REPLACE (str, ','), 0) AS cnt
FROM t_str_row) t,
(SELECT rownum cb FROM (SELECT 1 FROM dual group by cube (1, 2) c
WHERE c. cb <= t. cnt
Order by 1, 2;

Applicability: 10 Gb or later
SELECT t. id,
C. lv AS p,
Rtrim (regexp_substr (t. str | ',','.*? '|', ', 1, c. lv),', ') AS cv
FROM (SELECT id,
Str,
Length (regexp_replace (str | ',', '[^' | ',' | ']', NULL) AS cnt
FROM t_str_row) t
Inner join (select level lv FROM dual connect by level <= 5) c ON c. lv <=
T. cnt
Order by 1, 2;

4) Hierarchical + DBMS_RANDOM
Applicability: 10 Gb or later
SELECT id,
Level as p,
Rtrim (regexp_substr (str | ',','.*? '|', ', 1, LEVEL),', ') AS cv
FROM t_str_row
Connect by id = PRIOR id
And prior dbms_random.VALUE IS NOT NULL
And level <=
Length (regexp_replace (str | ',', '[^' | ',' | ']', NULL ))
Order by 1, 2;

5) Hierarchical + CONNECT_BY_ROOT
Applicability: 10 Gb or later
SELECT id,
Level as p,
Rtrim (regexp_substr (str | ',','.*? '|', ', 1, LEVEL),', ') AS cv
FROM t_str_row
Connect by id = connect_by_root id
And level <=
Length (regexp_replace (str | ',', '[^' | ',' | ']', NULL ))
Order by 1, 2;

6) MODEL
Applicability: 10 Gb or later
SELECT id, p, cv FROM t_str_row
MODEL
RETURN UPDATED ROWS
Partition by (ID)
Dimension by (0 AS p)
MEASURES (str | ',' AS cv)
RULES UPSERT
(Cv
[FOR p
FROM 1 TO length (regexp_replace (cv [0], '[^' | ',' | ']', null ))
INCREMENT 1
] = Rtrim (regexp_substr (cv [0], '. *? '|', ', 1, cv (p )),','))
Order by 1, 2;

End.