Oracle ranks Conversion Summary

2010-04-19  来源:本站原创  分类:Database  人气:257 

oracle sum up the ranks of conversion

Recent Forum raised the issue many people have a relationship with the ranks of conversion, so I converted the ranks of knowledge made a summary
Hope to be helpful, while thinning the wrong place, I implore you that
I am also in the process of writing to learn, be together and we learn from it.

The ranks of conversion includes the following six conditions:
* Column switch
* Bank transfer out
* Multi-column into a string
* Multi-line into a string
* String into multiple columns
* String into multiple lines

The following were examples of each.

At the outset that some cases are 10g and beyond need to have knowledge of:
a. Control model clauses,
b. Regular Expressions
c. Enhance the level of inquiry

The scope of the discussion only 8i, 9i, 10g and later. begin:

1, column switch
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: 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 the blank lines do not change, just add a where condition
WHERE COLUMN IS NOT NULL can.

2) MODEL
Scope: 10g and beyond
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
Scope: 8i, 9i, 10g and later to create an object and a collection:
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_row.c1),
cv_pair ('c2', t_col_row.c2),
cv_pair ('c3', t_col_row.c3))) t
ORDER BY 1, 2;

2, row turn out

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
Scope: 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;

MAX aggregate functions can also be used sum, min, avg, and other alternative aggregate functions.

Set out in the designated turn can only have one, but multiple columns can be fixed columns, see the following example:

SELECT mgr, deptno, empno, ename FROM 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 emp
WHERE mgr IN (7566, 7698)
AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;

Here transposed as empno, fixed as mgr, deptno.

There is also a line transfer out of the way is the same group in the row values into a single column value, but the switch to set the value of the same row as 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
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

This analysis functions can be achieved:

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
Scope: 8i, 9i, 10g and later this value for the row is not fixed, can be used.
Here I am writing a package, package
p_rows_column_real used above the first column is not limited to the conversion;
p_rows_column not limited to the aforementioned second column of the conversion.

CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
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_pivot_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_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS

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_pivot_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_pivot_ind_by IS TABLE OF VARCHAR2 (4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_by;

v_keep_cnt INT;
v_pivot_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_pivot_cnt: = length (p_pivot_cols) -
length (REPLACE (p_pivot_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_pivot_cnt LOOP
v_pivot (j): = f_split_str (p_pivot_cols, ',', j);
END LOOP;
v_sql: = 'select max (count (*)) from' | | p_table | | 'group by';
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_pivot.COUNT LOOP
v_partition2: = v_partition2 | | v_pivot (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_pivot.COUNT LOOP
v_sql: = v_sql | | 'max (decode (rn,' | | i | | ',' | | v_pivot (j) | |
', Null))' | | v_pivot (j) | | '_' | | i | | ',';
END LOOP;
END LOOP;
IF p_where IS NOT NULL THEN
v_sql: = rtrim (v_sql, ',') | | 'from (' | | v_partition | | '' | |
p_where | | ') group by';
ELSE
v_sql: = rtrim (v_sql, ',') | | 'from (' | | v_partition | |
') Group by';
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 FOR
SELECT 'x' FROM dual WHERE 0 = 1;
END;

PROCEDURE p_rows_column_real (p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_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_pivot_ind_by IS TABLE OF VARCHAR2 (4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_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_pivot_col | |
'As varchar2 (200)) as' | | p_pivot_col | | 'from' | | p_table | |
'Group by' | | p_pivot_col;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO v_pivot;
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_pivot.COUNT LOOP
v_sql: = v_sql | | 'max (decode (' | | p_pivot_col | | ',' | | chr (39) | |
v_pivot (x) | | chr (39) | | ',' | | p_pivot_val | |
', Null)) as "' | | v_pivot (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 FOR
SELECT 'x' FROM dual WHERE 0 = 1;
END;

END;
/

3. More columns into a string
CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;

This relatively simple, with | | or concat function can be achieved:
SELECT concat ('a', 'b') FROM dual;

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

SELECT ID, c1 ||','|| c2 ||','|| c3 AS c123
FROM t_col_str;

4. More rows into a string
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
Scope: 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
Scope: 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
Scope: 10g and 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
Scope: 8i, 9i, 10g and later
SELECT t.id 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;

Scope: 10g and later
SELECT t.id 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
Scope: 10g and later this function is predefined by ',' separated string, to use other symbols can be separated, replace the ',' replacement.

SELECT id, REPLACE (wmsys.wm_concat (col), ',', '/')
FROM t_row_str
GROUP BY id;

5. String into multiple columns is actually a string split their problem.

CREATE TABLE t_str_col AS
SELECT ID, c1 ||','|| c2 ||','|| c3 AS c123
FROM t_col_str;

SELECT * FROM t_str_col;

1) substr + instr
Scope: 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
Scope: 10g and 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. String into multiple lines
CREATE TABLE t_str_row AS
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
Scope: 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;

Scope: 10g and 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
Scope: 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
Such method is essentially to generate a continuous integer column, resulting in a continuous series for a lot of ways, including:
CONNECT BY, ROWNUM + all_objects, CUBE, etc..
Scope: 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;

Scope: 10g and 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
Scope: 10g and 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
Scope: 10g and 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
Scope: 10g and 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.

Turn on: http://trailblizer.blog.163.com/blog/static/5963036420098271121475/

More information: Oracle Database 11g New Features: Pivot and Unpivot introduced in Oracle Database 11g, you need to decode function for each value through any of the above, and write each different value for a separate column. However, this method is not intuitive.

Fortunately, you can now use a great new feature PIVOT operator through a new cross-table format to any query, the corresponding operator is called pivot. Here is the query written by:
http://oracle.chinaitlab.com/production/771287_2.html

相关文章
  • [Transfer] Oracle ranks conversion summary statement 2010-11-16

    oracle ranks conversion summary Before the start, accompanied by a short excerpt The time to write some SQL, and sometimes needs to be converted into columns or rows to columns conversion take place; Next, walnut blog ranks will be in Oracle to do so

  • oracle ranks conversion summary (reproduced) 2010-03-31

    oracle sum up the ranks of conversion Recent Forum raised the issue many people have a relationship with the ranks of conversion, so I converted the ranks of knowledge made a summary Hope to be helpful, while thinning the wrong place, I implore you t

  • Oracle ranks Conversion Summary 2010-04-19

    oracle sum up the ranks of conversion Recent Forum raised the issue many people have a relationship with the ranks of conversion, so I converted the ranks of knowledge made a summary Hope to be helpful, while thinning the wrong place, I implore you t

  • [Transfer] Oracle ranks of the converted statement summary 2010-11-16

    oracle conversion summary of the ranks Ranks of the conversion includes the following six conditions: * Column switch * Line transferred * Multi-column into a string * Multi-line string * Convert a string into multiple columns * Convert a string into

  • Online to see, Oracle ranks of conversion of a typical implementation, to find time to go in 9I and 10G under the effects test 2010-09-12

    Original Address: http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html Oracle ranks of conversion of a typical realization of several Study: NinGoo | [reproduced form shall be marked with the article hyperlink original source and

  • See online, Oracle ranks of conversion of a typical implementation, find time to go in 9I and 10G under the effects test 2010-09-12

    Original Address: http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html Oracle ranks of a typical conversion of several implementations Author: NinGoo | [reproduced form of a hyperlink should indicate the original source and autho

  • Oracle ranks of a typical conversion of several implementations 2010-09-16

    If the following table, each of which corresponds to the value of i number of rows is variable SQL> select * from t; IAD ---- ---- ------- 1 b 2008-03-27 10:55:42 1 a 2008-03-27 10:55:46 1 d 2008-03-27 10:55:30 2 z 2008-03-27 10:55:55 2 t 2008-03-27

  • Oracle Database issue summary (part of the switched network) 2010-06-03

    1.Oracle basic types of databases that when you create a data table in the database, you need to define the type of all fields in the table. ORACLE There are many kinds of data types to meet your needs. Some data types are divided into: character, nu

  • oracle in a summary of long type 2010-02-11

    oracle in a summary of long type 1, LONG data type is stored in variable-length string, maximum length limit is 2GB. 2, for beyond a certain length of text, the basic type can only be used to store LONG, many of the data dictionary definition of the

  • Oracle Flashback Query summary 2010-05-08

    Oracle Flashback Query summary 1, Oracle 9i or later flashback query feature described In Oracle 9i before the operation if the user error data, in addition to not fully recovered, there is no good way, but can help DBA to restore the database, but t

  • sql ranks conversion [transfer] 2010-09-06

    Description: General ranks conversion (version 1.0) only for sql server 2000 to provide static and dynamic written, version 2.0 increase in sql server 2005 in the wording. Question: Suppose Zhang Xuesheng transcript (tb) as follows: Name of the langu

  • Oracle Group Functions Summary 2010-09-19

    Oracle Group Functions Summary 1.OVER (PARTITION BY ..) Cases: select a, b, c, sum (c) OVER (PARTITION BY b) sum_c The value of the b column c the same value of the cumulative row. - Retrieve the type specified number of percentage Lingshou Hu select

  • Oracle synonyms (synonyms) Summary 2010-12-31

    Oracle synonyms (synonyms) Summary Study: 6ysnow, Source: blog, Editor: TANG Xiao, if, 2008-09-01 09:02 Oracle synonyms (synonyms) is the alias from the meaning literally, and trying to function similar to a kind of mapping. This article describes ho

  • Oracle ranks of conversion of a typical realization of several 2010-09-16

    If the following table, in which the i values corresponding to the number of rows is variable SQL> select * from t; IAD ---- ---- ------- 1 b 2008-03-27 10:55:42 1 a 2008-03-27 10:55:46 1 d 2008-03-27 10:55:30 2 z 2008-03-27 10:55:55 2 t 2008-03-27 1

  • Summary oracle implicit conversion rules 2010-12-22

    In oracle, if the association between different data types, if not explicitly convert the data, it will be the data according to the following rules of implicit conversion 1) For INSERT and UPDATE operations, oracle will insert or update value of the

  • The ranks of the spell ORACLE string conversion 2010-06-30

    Some time ago using ORACLE, colleagues proposed a requirement calling for the same value CODE connection into a string. How can such a small problem baffled me, so proposals! Table statement : create table SP_ALERT_PA_GROUP ( ID INTEGER not null, ALE

  • Oracle common function summary 2010-06-14

    PL / SQL single-line functions and group functions Xiangjie Function is a zero or more parameters and a return value of the program. Oracle built-in SQL, a series of functions that can be called SQL or PL / SQL statement, Function is divided into two

  • oracle implicit conversion rules are summarized 2010-12-22

    In oracle, if the association between different data types, if not explicitly convert the data, it will be according to the following rules for implicit conversion of data 1) For INSERT and UPDATE operations, oracle will insert or update value is imp

  • Oracle ranks of the vertical exchange of horizontal table and the table 2011-03-04

    SQL code / * In actual use the sql work will always come across the title of a column value into the display. Is always said that the ranks of the conversion or exchange. For example the following data: ID NAME KECHENG CHENGJI - ---------- ----------

  • ORACLE EXPLAIN PLAN Summary 2010-02-22

    In the ORACLE database, need to optimize the SQL statements would need to know its implementation plan, which targeted adjustments. ORACLE access implementation plan There are several ways to sum up the following under the 1, EXPLAIN the use of Oracl