Oracle row and Column conversions summary

The row and column conversions include the following six scenarios:

* List of career change

* Row to Column

* Multiple columns converted into strings

* Multiple lines converted into strings

* String converted into multiple columns

* String converted into multiple lines

The following examples are presented separately.

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

A. Master the Model clause

B, Regular expression

c, strengthen the level of query

The scope of application of the discussion includes only 8i,9i,10g and later versions. Begin

1. List of career change

The results before a career change are as follows:

The effect of a career change is as follows:

SQL code:

CREATE TABLET_col_row (IDINT, C1VARCHAR2(Ten), C2VARCHAR2(Ten), C3VARCHAR2(Ten)); INSERT  intoT_col_rowVALUES(1,'v11','v21','v31');INSERT  intoT_col_rowVALUES(2,'V12','v22',NULL);INSERT  intoT_col_rowVALUES(3,'V13',NULL,'v33');INSERT  intoT_col_rowVALUES(4,NULL,'v24','v34');INSERT  intoT_col_rowVALUES(5,'V15',NULL,NULL);INSERT  intoT_col_rowVALUES(6,NULL,NULL,'V35');INSERT  intoT_col_rowVALUES(7,NULL,NULL,NULL);COMMIT; SELECT *  fromT_col_row;

1). UNION all–> Scope of application: 8i,9i,10g and later versions
SQL code:

 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 blank line does not need to be converted, simply add a where condition,

SQL code:

WHERE COLUMN  is  not NULL  

2). model–> Scope of application: 10g and later

SELECTID, CN, CV fromT_col_rowmodelRETURNUPDATED rowspartition by(ID) DIMENSION by(0  asN) MEASURES ('xx'  ascn'yyy'  asCV,C1,C2,C3)--xx, yyy indicates field lengthRULES UPSERT All(CN[1] = 'C1', CN[2] = 'C2', CN[3] = 'C3', CV[1] =C1[0], CV[2] =C2[0], CV[3] =C3[0])ORDER  byID,CN;

Now small analysis of the above query:

Partition by (PRD_TYPE_ID) specifies that the result is based on the prd_type_id partition.

Dimension by (0 as N) defines the length of the array, which means that an array index must be provided to access the cells in the array.

Measures (' xx ' as CN) indicates that each cell in the array contains a number, indicating that the array is named CN.

3) .collection-> Scope of application: 8i,9i,10g and later version

To create an object and a collection:

SQL statements:

CreateTYPE Cn_pair asOBJECT (CNVARCHAR(Ten), CVVARCHAR2(Ten));CREATETYPE Cv_varr asVaray (8) ofCv_pair;Selectid,t.cn asCn,t.cv asCV fromT_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))) TORDER  by 1,2

2, Row to column

The effect is the following before a row is turned:

Row-to-column effects are as follows:

CREATE TABLET_row_col asSELECTId'C1'cn, C1 CV fromT_col_rowUNION  AllSELECTId'C2'cn, C2 CV fromT_col_rowUNION  AllSELECTId'C3'CN, C3 CV fromT_col_row;SELECT *  fromT_row_colORDER  by 1,2;

1) AGGREGATE FUNCTION, applicable scope: 8i,9i,10g and later version

SelectID,Max(Decode (CN,'C1', CV,NULL)) ascl,Max(Decode (CN,'C2', CV,NULL)) asC2,Max(Decode (CN,'C3', CV,NULL)) asC3 fromT_row_colGroup  byIDOrder  by 1

The Max aggregation function can also be substituted with other aggregation functions such as sum,min,avg.

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

SelectMgr,depton,ename fromScott.empOrder  by 1,2;SelectMgr, 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" fromscott.empwhereMgrinch(7566,7698)           andDeptnoinch( -, -)Group  byMgr,deptnoOrder  by 1,2                      

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

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

Reference Source: http://www.cnblogs.com/linjiqin/p/3148808.html

Summary of Oracle row and column conversions