1. # Oracle行列互换总结

blog文档结构图：

1. 列转行
2. 行转列
3. 多列转换成字符串
4. 多行转换成字符串
5. 字符串转换成多列
6. 字符串转换成多行

A. 掌握model子句

B. 正则表达式

C. 加强的层次查询

1. ## 列转行

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 ---主要方法

1. #### 例一

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;

WHERE COLUMN IS NOT NULL 即可。

SELECT id, 'c1' cn, c1 cv

FROM t_col_row

where c1 is not null

UNION ALL

SELECT id, 'c2' cn, c2 cv

FROM t_col_row

where c2 is not null

UNION ALL

SELECT id, 'c3' cn, c3 cv

FROM t_col_row

where c3 is not null;

1. #### 例二

create table TEST_LHR

(

NAME VARCHAR2(255),

JANUARY NUMBER(18),

FEBRUARY NUMBER(18),

MARCH NUMBER(18),

APRIL NUMBER(18),

MAY NUMBER(18)

)

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('长寿', 58, 12, 26, 18, 269);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('璧山', 33, 18, 17, 16, 206);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('杨家坪', 72, 73, 79, 386, 327);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('巫溪', 34, 9, 7, 21, 33);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('丰都', 62, 46, 39, 36, 91);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('武隆', 136, 86, 44, 52, 142);

commit;

SELECT *

FROM (SELECT t.name,

'january' MONTH,

t.january v_num

FROM TEST_LHR t

UNION ALL

SELECT t.name,

'february' MONTH,

t.february v_num

FROM TEST_LHR t

UNION ALL

SELECT t.name,

'march' MONTH,

t.march v_num

FROM TEST_LHR t

UNION ALL

SELECT t.name,

'april' MONTH,

t.april v_num

FROM TEST_LHR t

UNION ALL

SELECT t.name,

'may' MONTH,

t.may v_num

FROM TEST_LHR t)

ORDER BY NAME;

1. ### insert all into ... select

SQL> desc test_lhr1

Name Type Nullable Default Comments

----- ------------- -------- ------- --------

NAME VARCHAR2(255) Y

MONTH VARCHAR2(8) Y

V_NUM NUMBER(18) Y

insert all

into test_lhr1(NAME,month,v_num) values(name, 'may', may)

into test_lhr1(NAME,month,v_num) values(name, 'april', april)

into test_lhr1(NAME,month,v_num) values(name, 'february', february)

into test_lhr1(NAME,month,v_num) values(name, 'march', march)

into test_lhr1(NAME,month,v_num) values(name, 'january', january)

select t.name,t.january,t.february,t.march,t.april,t.may from test_lhr t;

commit;

select * from test_lhr1;

1. ### MODEL

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;

1. ### 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;

1. ## 行转列

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(max+decode) ---主要方法

1. #### 例一

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;

? 注意：

1. MAX聚集函数也可以用sum、min、avg 等其他聚集函数替代。
2. 被指定的转置列只能有一列，但固定的列可以有多列，如果转置列有多列可以有2种办法解决，① 采用1.3.2创建临时表的方式

② 可以先转为单列，单列采用字符串的格式，然后将单列转换为多列

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;

1、固定列数的行列转换

---------------------------

student1 语文 80

student1 数学 70

student1 英语 60

student2 语文 90

student2 数学 80

student2 英语 100

……

student1 80 70 60

student2 90 80 100

……

select student,sum(decode(subject,'语文', grade,null)) "语文",

from table

group by student

2、不定列行列转换

c1 c2

--------------

1 我

1 是

1 谁

2 知

2 道

3 不

……

1 我是谁

2 知道

3 不

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)

RETURN VARCHAR2

IS

Col_c2 VARCHAR2(4000);

BEGIN

FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP

Col_c2 := Col_c2||cur.c2;

END LOOP;

Col_c2 := rtrim(Col_c2,1);

RETURN Col_c2;

END;

/

SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可

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

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;

1. #### 例二

SELECT t.name,

MAX(decode(t.month, 'may', t.v_num)) AS may,

MAX(decode(t.month, 'april', t.v_num)) AS april,

MAX(decode(t.month, 'february', t.v_num)) AS february,

MAX(decode(t.month, 'march', t.v_num)) AS march,

MAX(decode(t.month, 'january', t.v_num)) AS january

FROM test_lhr1 t

GROUP BY t.name;

1. #### 延伸

SELECT *

FROM test_lhr1 t

ORDER BY t.name,

t.month;

SELECT t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

COUNT(t.v_num) count_num

FROM test_lhr1 t

GROUP BY t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

END;

MAX(decode(t2.name, '璧山', t2.count_num)) 璧山,

MAX(decode(t2.name, '长寿', t2.count_num)) 长寿,

MAX(decode(t2.name, '武隆', t2.count_num)) 武隆,

MAX(decode(t2.name, '丰都', t2.count_num)) 丰都,

MAX(decode(t2.name, '杨家坪', t2.count_num)) 杨家坪

FROM (SELECT t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

COUNT(t.v_num) count_num

FROM test_lhr1 t

GROUP BY t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

END) t2

1. ### 创建临时表

SELECT t1.id,

t1.cv C1,

t2.cv C2,

t3.cv C3

FROM (SELECT * FROM t_row_col t WHERE t.cn = 'c1') t1,

(SELECT * FROM t_row_col t WHERE t.cn = 'c2') t2,

(SELECT * FROM t_row_col t WHERE t.cn = 'c3') t3

WHERE t1.id = t2.id

AND t2.id = t3.id;

1. ### PL/SQL --存过

p_rows_column_real 用于前述的第一种不限定列的转换；

p_rows_column 用于前述的第二种不限定列的转换。

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;

/

1. ## 多列转换成字符串

CREATE TABLE t_col_str AS

SELECT * FROM t_col_row;

SELECT concat('a','b') FROM dual;

1. ### || OR CONCAT

SELECT * FROM t_col_str;

SELECT ID,

c1 || ',' || c2 || ',' || c3 AS c123

FROM t_col_str;

1. ## 多行转换成字符串

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

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;

1. ### ROW_NUMBER + LEAD

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;

1. ### MODEL

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;

1. ### SYS_CONNECT_BY_PATH --主要方法

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

CONNECT BY rn = PRIOR rn + 1

AND id = PRIOR id

GROUP BY t.id;

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

CONNECT BY rn = PRIOR rn + 1

AND id = PRIOR id;

1. ### WMSYS.WM_CONCAT(col) +dbms_lob.substr(clobcloum,2000,1) --主要方法

SELECT id,

REPLACE(wmsys.wm_concat(col), ',', '/') str

FROM t_row_str

GROUP BY id;

SELECT id,

dbms_lob.substr(wm_concat(DISTINCT col), 2000, 1)

FROM t_row_str t

GROUP BY t.id;

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row insertedSQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select * from idtable;

ID NAME---------- ------------------------------ 10 ab 10 bc 10 cd 20 hi 20 ij 20 mn

6 rows selectedSQL> select id,wmsys.wm_concat(name) name from idtable2 group by id;

ID NAME---------- -------------------------------------------------------------------------------- 10 ab,bc,cd 20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

ID NAME---------- -------------------------------------------------------------------------------- 10 ab,bc,cd 10 ab,bc,cd 10 ab,bc,cd 20 ab,bc,cd,hi,ij,mn 20 ab,bc,cd,hi,ij,mn 20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

ID NAME---------- -------------------------------------------------------------------------------- 10 ab 10 ab,bc 10 ab,bc,cd 20 ab,bc,cd,hi 20 ab,bc,cd,hi,ij 20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

ID NAME---------- -------------------------------------------------------------------------------- 10 ab,bc,cd 10 ab,bc,cd 10 ab,bc,cd 20 hi,ij,mn 20 hi,ij,mn 20 hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

ID NAME---------- -------------------------------------------------------------------------------- 10 ab 10 bc 10 cd 20 hi 20 ij 20 mn

6 rows selected

1. #### 注意

Create Table aa nologging As

SELECT id,

to_char(wm_concat(DISTINCT col)) colss

FROM t_row_str t

GROUP BY t.id;

insert into aa

SELECT id,

to_char(wm_concat(DISTINCT col)) colss

FROM t_row_str t

GROUP BY t.id;

1. 通过动态sql语句执行

execute immediate 'INSERT INTO aa

SELECT id,

to_char(wm_concat(DISTINCT col)) colss

FROM t_row_str t

GROUP BY t.id';

SELECT id,

to_char(wm_concat(col)) colss

FROM (SELECT DISTINCT a.id,

a.col

FROM t_row_str a) t

GROUP BY t.id;

1. ### 通过分析函数实现行转列

1. #### 原理

2.1　分析函数的格式及语法

Analytic-Function（，，...）

OVER （）

（2）Arguments：参数，分析函数通常有0到3个参数，参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。对于用户自定义的参数，可以根据实际情况使用。

（3）OVER：是分析函数就必须使用的关键字，对于既可作为聚集函数又可作为分析函数的函数，Oracle无法识别，必须用over来标识此函数为分析函数。

（4）Query-Partition-Clause：查询分组子句，根据划分表达式设置的规则，PARTITION BY将一个结果逻辑分成N个分组划分表达式。分析函数独立应用于各个分组，并在应用时重置。

（5）Order-By-Clause：（按…排序分组），是排序子句，根据一个或多个排序表达式对分组进行排序。

（6）Windowing-Clause窗口生成语句：窗口生成语句用以定义滑动或固定数据窗口，分析函数在分组内进行分析。该语句能够对分组中任意定义的滑动或固定窗口进行计算。

2.2　实例原理介绍

1. #### 实例

1)创建临时表

Drop Table temp;

Create Table temp

(

num varchar2(15),name varchar2(20),

sex varchar2(2),

classes varchar2(30),

course_name varchar2(50)

);

-----------------------------------------------------------------------------------

2)构造数据

insert into temp(num,name,sex,classes,course_name) values ('206211','王艺','男','06-1班','保险学');

insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','保险学');

insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','财务管理');

insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','财务会计');

insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','电子商务');

insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','公共经济学');

insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','公司理财');

insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','管理学原理');

insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','保险学');

insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','保险学');

insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','财务管理');

insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','财务会计');

insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','电子商务');

insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','公共经济学');

insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','环境管理学');

insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','管理学原理');

insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','商务谈判');

insert into temp(num,name,sex,classes,course_name) values ('206216','李佳琪','男','06-2','土地估计');

Commit;

select * from temp;

-----------------------------------------------------------------------------------

3)先查一下course_name最多的组合

select max(count(course_name))

from temp

group by num,name,sex,classes;

-----------------------------------------------------------------------------------

4) 列的位置

用分析函数中的row_number函数,在num,name,sex,classes相同的情况下course_name所处的列的位置(第几列)

row_number函数解释：返回有序组中一行的偏移量,从而可用于按特定标准排序的行号。

SELECT num,

NAME,

sex,

classes,

course_name,

row_number() over(PARTITION BY num, NAME, sex, classes ORDER BY course_name) rn 　from temp;

-----------------------------------------------------------------------------------

5) 把course_name的所有的行换成列

SELECT num,

NAME,

sex,

classes,

MAX(decode(rn, 1, course_name, NULL)) course_name_1,

MAX(decode(rn, 2, course_name, NULL)) course_name_2,

MAX(decode(rn, 3, course_name, NULL)) course_name_3,

MAX(decode(rn, 4, course_name, NULL)) course_name_4,

MAX(decode(rn, 5, course_name, NULL)) course_name_5

FROM (SELECT num,

NAME,

sex,

classes,

course_name,

row_number() over(PARTITION BY num, NAME, sex, classes ORDER BY course_name) rn

FROM temp)

GROUP BY num,

NAME,

sex,

classes;

6）把转换后的name拼成一个字符串，放在一行

SELECT num,

NAME,

sex,

classes,

(MAX(decode(rn, 1, course_name, NULL)) ||

MAX(decode(rn, 2, ',' || course_name, NULL)) ||

MAX(decode(rn, 3, ',' || course_name, NULL)) ||

MAX(decode(rn, 4, ',' || course_name, NULL)) ||

MAX(decode(rn, 5, ',' || course_name, NULL))) NAME

FROM (SELECT num,

NAME,

sex,

classes,

course_name,

row_number() over(PARTITION BY num, NAME, sex, classes ORDER BY course_name) rn

FROM temp)

GROUP BY num,

NAME,

sex,

classes;

1. ### 例题

CREATE TABLE tab_name(ID INTEGER NOT NULL PRIMARY KEY,cName VARCHAR2(20));

CREATE TABLE tab_name2(ID INTEGER NOT NULL,pName VARCHAR2(20));

INSERT INTO tab_name(ID,cName) VALUES (1,'百度');

INSERT INTO tab_name(ID,cName) VALUES (2,'Google');

INSERT INTO tab_name(ID,cName) VALUES (3,'网易');

INSERT INTO tab_name2(ID,pName) VALUES (1,'研发部');

INSERT INTO tab_name2(ID,pName) VALUES (1,'市场部');

INSERT INTO tab_name2(ID,pName) VALUES (2,'研发部');

INSERT INTO tab_name2(ID,pName) VALUES (2,'平台架构');

INSERT INTO tab_name2(ID,pName) VALUES (3,'研发部');

COMMIT;

select * from tab_name;

select * from tab_name2;

SELECT t1.ID,

t1.cName,

wmsys.wm_concat(t2.pName)

FROM tab_name t1,

tab_name2 t2

WHERE t1.ID = t2.ID

GROUP BY t1.cName,

t1.id;

SELECT id,

cName,

ltrim(MAX(sys_connect_by_path(pName, ',')), ',')

FROM (SELECT row_number() over(PARTITION BY t1.id ORDER BY cName) r,

t1.*,

t2.pName

FROM tab_name t1,

tab_name2 t2

WHERE t1.id = t2.id)

CONNECT BY PRIOR r = r - 1

AND PRIOR id = id

GROUP BY id,

cName

ORDER BY id;

CREATE OR REPLACE FUNCTION coltorow(midId INT) RETURN VARCHAR2 IS

RESULT VARCHAR2(1000);

BEGIN

FOR cur IN (SELECT pName FROM tab_name2 t2 WHERE midId = t2.id) LOOP

RESULT := RESULT || cur.pName || ',';

END LOOP;

RESULT := rtrim(RESULT, ',');

RETURN(RESULT);

END coltorow;

SELECT t1.*,

coltorow(t1.ID)

FROM tab_name t1,

tab_name2 t2

WHERE t1.ID = t2.ID

GROUP BY t1.ID,

t1.cname

ORDER BY t1.ID;

1. ## 字符串转换成多列

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

1. #### 将单列分为多列

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;

1. 存过

1. 借助excel办公软件

1. 采用sqlldr

1. #### 将多列分为多列

CREATE TABLE t_str_col2 AS

SELECT ID,c1||','||c2||','||c3 AS c123,c3||','||c2 as c32

FROM t_col_str;

select * from t_str_col2;

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,

c32,

substr(c32, 1, instr(c32, ',') - 1) cc3,

substr(c32, instr(c32, ',') + 1) cc2

FROM t_str_col2

ORDER BY 1;

1. ### REGEXP_SUBSTR

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;

1. ## 字符串转换成多行

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

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;

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;

1. ### VARRAY

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;

1. ### SEQUENCE SERIES

CONNECT BY,ROWNUM+all_objects,CUBE 等。

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;

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;

drop table t_test

create table t_test (id number, names varchar2(200));

insert into t_test values (1,'a1,a2,a3,a4');

insert into t_test values (2,'b1,b2,b3');

insert into t_test values (3,'c1,c2,c3,c4,c5');

commit;

select * from t_test;

SELECT id,

REGEXP_SUBSTR(names, '[^,]+', 1, l) AS NAME

FROM t_test,

(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 100)

WHERE l <= LENGTH(names) - LENGTH(REPLACE(names, ',')) + 1

ORDER BY 1,

2;

SELECT DISTINCT REGEXP_SUBSTR(PARAM_VALUES, '[^,]+', 1, LEVEL) AS SOC_NAME  FROM CM9_BATCH_CONTROLWHERE PARAM_NAME = 'OFFER'   AND JOB_NAME = 'xxxxxxx'   AND JOB_REC = 'ENDDAY'CONNECT BY REGEXP_SUBSTR((SELECT PARAM_VALUES                           FROM CM9_BATCH_CONTROL                          WHERE JOB_NAME = 'xxxxx'                            AND PARAM_NAME = 'OFFER'),                         '[^,]+',                         1,                         LEVEL) IS NOT NULL;

SELECT CO.SOC_CD FROM (SELECT REGEXP_SUBSTR(PARAM_VALUES,'[^,]+',1,l) AS SOC_NAME  FROM CM9_BATCH_CONTROL      ,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100)WHERE PARAM_NAME = 'OFFER'   AND JOB_NAME = 'xxxx'   AND JOB_REC = 'ENDDAY'   AND l <=LENGTH(PARAM_VALUES) - LENGTH(REPLACE(PARAM_VALUES,','))+1)T, CSM_OFFER CO WHERE T.SOC_NAME = CO.SOC_NAMEand T.SOC_NAME is not null

1. ### HIERARCHICAL + DBMS_RANDOM

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;

1. ### HIERARCHICAL + CONNECT_BY_ROOT

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;

1. ### MODEL

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 ;

1. # 例题

drop table course;

create table course (stname varchar(10), math int, english int);

insert into course values('Jame', 65, 97);

insert into course values('Tom',88,59);

insert into course values('calvin',98,99);

select * from course;

create table pivot (id int);

insert into pivot values (1);

insert into pivot values (2);

select * from pivot;

select * from course;

select * from pivot;

SELECT stname,

CASE id

WHEN 1 THEN

'Math'

WHEN 2 THEN

'English'

ELSE

'0'

END AS subject,

CASE id WHEN 1 THEN math WHEN 2 THEN english ELSE 0end AS grade

FROM course,

pivot;

 ............................................................................................................................... ● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用 ● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 联系我请加QQ好友(646634621)，注明添加缘由 ● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................... 拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

QQ：646634621| 网名：小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群：私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用（RAC+DG+OGG）网络班开讲啦，有需要的小伙伴可以私聊我。

• 博文量
1367
• 访问量
8348721