Oracle's Row and column conversions

The row and column conversions include the following six scenarios:

1. Column career Change

2. Row-Turn column

3. Multiple columns converted to strings

4. Convert multiple lines into strings

5. Convert strings to multiple columns

6. Convert strings to multiple lines

First of all, some examples require the following 10g and subsequent knowledge:

A. Master the Model clause,

B. Regular expressions

C. Enhanced level of inquiry

1, the column to change careers

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, NULL, NULL);

COMMIT;

SELECT * from T_col_row;

1 UNION All: Scope of application: 8i,9i,10g and future versions

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. A null row does not need to be converted, and only one where condition is added where the where COLUMN is not NULL.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

2) MODEL

Scope of application: 10g and 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: Applicable scope: 8i,9i,10g and later version

To create an object and a collection:

CREATE TYPE Cv_pair as OBJECT (CN VARCHAR2), 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_ROW.C1),

Cv_pair (' C2 ', T_COL_ROW.C2),

Cv_pair (' C3 ', t_col_row.c3)) t

Order by 1, 2;

2. Row-Turn column

CREATE TABLE T_row_col as

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: Applicable scope: 8i,9i,10g and later version

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 aggregation function can also be substituted with other aggregate functions such as SUM, Min, and Avg.

The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.

Use sign and decode to complete the comparison field size to a field

Select Decode (sign (Field 1-field 2),-1, Field 3, Field 4) from dual;

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

SELECT Mgr, Deptno, empno, ename from Scott.emp order by 1, 2;

SELECT Mgr,

Deptno

MAX (Decode (empno, ' 7788 ', ename, NULL)) "7788",

MAX (Decode (empno, ' 7902 ', ename, NULL)) "7902",

MAX (Decode (empno, ' 7844 ', ename, NULL)) "7844",

MAX (Decode (empno, ' 7521 ', ename, NULL)) "7521",

MAX (Decode (empno, ' 7900 ', ename, NULL)) "7900",

MAX (Decode (empno, ' 7499 ', ename, NULL)) "7499",

MAX (Decode (empno, ' 7654 ', ename, NULL)) "7654"

From Scott.emp

WHERE Mgr in (7566, 7698)

and Deptno in (20, 30)

GROUP by Mgr, Deptno

Order by 1, 2;

Here the transpose is listed as empno, fixed as mgr,deptno.

There is also a way to row, where the row values in the same group change to a single column value, but the value of the transpose row does not change to the column name:

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