首页 / ORACLE / Oracle行列互换总结
Oracle行列互换总结
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle行列互换总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含35869字，纯文字阅读大概需要52分钟。
内容图文
blog文档结构图：
最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，希望对大家有所帮助，同时有何错疏，恳请大家指出，我也是在写作过程中学习，算是一起和大家学习吧！
行列转换包括以下六种情况：
-
列转行
-
行转列
-
多列转换成字符串
-
多行转换成字符串
-
字符串转换成多列
-
字符串转换成多行
下面分别进行举例介绍。
首先声明一点，有些例子需要如下10g 及以后才有的知识：
A. 掌握model子句
B. 正则表达式
C. 加强的层次查询
讨论的适用范围只包括8i,9i,10g 及以后版本。
-
列转行
首先需要明白什么是列转行，简单的说就是将原表中的列名作为转换后的表的内容，这就是列转行。
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;
-
UNION ALL ---主要方法
适用范围：8i,9i,10g 及以后版本
-
例一
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条件，
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;
-
例二
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;
-
insert all into ... select
首先创建需要的表，test_lhr1，
SQL> desc test_lhr1
Name Type Nullable Default Comments
----- ------------- -------- ------- --------
NAME VARCHAR2(255) Y
MONTH VARCHAR2(8) Y
V_NUM NUMBER(18) Y
然后执行下边的sql语句：
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;
别忘记commit操作，然后再查询test_lhr1，发现表中的数据就是列转成行了。
select * from test_lhr1;
-
MODEL
适用范围：10g 及以后
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;
-
COLLECTION
适用范围：8i,9i,10g 及以后版本
要创建一个对象和一个集合：
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;
-
行转列
行转列就是将行数据内容作为列名。
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;
-
AGGREGATE FUNCTION(max+decode) ---主要方法
用聚集函数来实现，适用范围：8i,9i,10g 及以后版本
-
例一
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聚集函数也可以用sum、min、avg 等其他聚集函数替代。
-
被指定的转置列只能有一列，但固定的列可以有多列，如果转置列有多列可以有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;
这里转置列为empno，固定列为mgr，deptno。
1、固定列数的行列转换
如
student subject grade
---------------------------
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)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
2、不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
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;
-
例二
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;
-
延伸
如果要实现对各个不同的区间进行统计，则：
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'
END AS grade,
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;
SELECT t2.grade,
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'
END AS grade,
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
GROUP BY t2.grade;
-
创建临时表
基本的行转列可以通过创建临时表来实现，首先将基础表根据条件创建几个临时表，然后将这些临时表关联起来就可以了。
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;
-
PL/SQL --存过
适用范围：8i,9i,10g 及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包，包中
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;
/
-
多列转换成字符串
CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;
这个比较简单，用|| 或concat 函数可以实现：
SELECT concat('a','b') FROM dual;
-
|| OR CONCAT
适用范围：8i,9i,10g 及以后版本
SELECT * FROM t_col_str;
SELECT ID,
c1 || ',' || c2 || ',' || c3 AS c123
FROM t_col_str;
-
多行转换成字符串
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;
-
MAX + DECODE
适用范围：8i,9i,10g 及以后版本
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;
-
ROW_NUMBER + LEAD
适用范围：8i,9i,10g 及以后版本
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;
-
MODEL
适用范围：10g 及以后版本
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;
-
SYS_CONNECT_BY_PATH --主要方法
适用范围：8i,9i,10g 及以后版本
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;
适用范围：10g 及以后版本
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;
-
WMSYS.WM_CONCAT(col) +dbms_lob.substr(clobcloum,2000,1) --主要方法
适用范围：10g 及以后版本
这个函数预定义按',' 分隔字符串，若要用其他符号分隔可以用，replace 将',' 替换。
或者： to_char(wmsys.wm_concat(dic.COLUMN_NAME))
SELECT id,
REPLACE(wmsys.wm_concat(col), ',', '/') str
FROM t_row_str
GROUP BY id;
将clob转换为字符串类型为：
SELECT id,
dbms_lob.substr(wm_concat(DISTINCT col), 2000, 1)
FROM t_row_str t
GROUP BY t.id;
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果 select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;DEPTNO ENAME------ ---------- 10 CLARK, KING, MILLER 20 ADAMS, FORD, JONES, SCOTT, SMITH 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
例子如下：
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
-
注意
以下均不报错：
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;
但是以上语句放在存过中报错，编译不能通过：
解决办法：
-
通过动态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;
-
通过分析函数实现行转列
对数据库中的数据用SQL实现行列转换，不但需要编写复杂的程序代码，还需要编写存储过程。若引入ORACLE中的分析函数则会使该过程简便很多。首先找出表中所有关键字的属性个数的最大值，设为n，其次为每个关键字新添加n列，并用分析函数查询关键字的属性所处列的位置，然后将每个关键字的多行属性转换成多列属性，最后把生成的多个新列拼成一个串形成一列，从而实现行列转换。
-
引言
分析函数的设计目的是为了解决诸如"累计计算"等问题。虽然大部分的问题都可以用PL/SQL解决，但是性能并不理想，首先查询本身并不容易编写，其次有些很难在SQL中直接做的查询但实际上是很普通的操作，比如实现数据表中行列传换。这样的问题在SQL中做查询就很困难。在分析函数出现以前，我们必须使用自联查询或者子查询甚至复杂的存储过程实现的语句，现在只要一条简单的SQL语句就可以实现了，而且在执行效率方面也有相当大的提高。本文将以一个实例来描述如何采用分析函数实现数据中的行列互换。
-
原理
2.1 分析函数的格式及语法
分析函数是在一个记录行分组的基础上计算它们的总值。行的分组被称窗口，并通过分析语句定义。对于每记录行，定义了一个"滑动"窗口。该窗口确定"当前行"计算的范围。窗口的大小可由各行的实际编号或由时间等逻辑间隔确定。
分析函数以如下形式开头：
Analytic-Function（，，...）
OVER （）
（1）Analytic-Function：分析函数的名称，Oracle10gR2带的内置分析函数有多个，包括：AVG、CORR、COVAR_POP、COVAR_SAMP、COUNT、LAG、LAST、LEAD、MAX、MIN、RANK、SUM等；对于用户自定义的分析函数，分析函数名称需要满足标识符规则。
（2）Arguments：参数，分析函数通常有0到3个参数，参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。对于用户自定义的参数，可以根据实际情况使用。
（3）OVER：是分析函数就必须使用的关键字，对于既可作为聚集函数又可作为分析函数的函数，Oracle无法识别，必须用over来标识此函数为分析函数。
（4）Query-Partition-Clause：查询分组子句，根据划分表达式设置的规则，PARTITION BY将一个结果逻辑分成N个分组划分表达式。分析函数独立应用于各个分组，并在应用时重置。
（5）Order-By-Clause：（按…排序分组），是排序子句，根据一个或多个排序表达式对分组进行排序。
（6）Windowing-Clause窗口生成语句：窗口生成语句用以定义滑动或固定数据窗口，分析函数在分组内进行分析。该语句能够对分组中任意定义的滑动或固定窗口进行计算。
2.2 实例原理介绍
本实例是将具有相同关键字的多条记录中的某一不同列合并成一列，例如在一个临时表中包含有用户的编号、电话号码、产品名称、所在营业区以及相关业务名称5个字段，而每个用户的业务可能有多项，这样创建数据表将会造成冗余，现在要想办法将表中编号、电话号码、产品名称、所在营业区四个字段相同的用户的相关业务属性合并成一列解决冗余问题，使用SQL语句会比较困难，甚至需要一定的存储过程。使用Orcale中的分析函数来实现这样的行列转换就比较简单方便了。
-
实例
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;
-
总结
本文中的程序能够实现以下功能：①计算具有相同关键字的最多的组合；②根据分析函数查询某一关键字所处的列的位置；③把需合并列的所有的行换成列；④把需要合并的某几列拼成一个串。
分析函数除了拥有以上所介绍的功能，还能够实现诸如求和、Top-N查询、统计某个范围的数据行窗口、交叉表查询等功能。
-
例题
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;
方法一:使用wmsys.wm_concat()
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;
方法二:使用sys_connect_by_path
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)
START WITH r = 1
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;
-
字符串转换成多列
其实际上就是一个字符串拆分的问题。
CREATE TABLE t_str_col AS
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;
SELECT * FROM t_str_col;
-
SUBSTR + INSTR
-
将单列分为多列
-
适用范围：8i,9i,10g 及以后版本
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;
-
存过
将单列分为多列见以下存过
-
借助excel办公软件
首先将需要转换的字符串列导出到一个txt文件或其它文件中，然后新建一个excel文件，打开这个excel文件后，在数据选项卡里选择导入数据，见截图：
金山WPS
微软办公软件
选择字符格式
选择分隔符号
最后确定就OK了，最后把excel里转换后的数据通过复制粘贴的方式导入到数据库里就可以了。
-
采用sqlldr
采用sqlldr的方式导入到数据库中。
-
将多列分为多列
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;
-
REGEXP_SUBSTR
适用范围：10g 及以后版本
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;
-
字符串转换成多行
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;
-
UNION ALL
适用范围：8i,9i,10g 及以后版本
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;
适用范围：10g 及以后版本
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;
-
VARRAY
适用范围：8i,9i,10g 及以后版本
要创建一个可变数组：
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;
-
SEQUENCE SERIES
这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：
CONNECT BY,ROWNUM+all_objects,CUBE 等。
适用范围：8i,9i,10g 及以后版本
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;
适用范围：10g 及以后版本
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
-
HIERARCHICAL + DBMS_RANDOM
适用范围：10g 及以后版本
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;
-
HIERARCHICAL + CONNECT_BY_ROOT
适用范围：10g 及以后版本
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;
-
MODEL
适用范围：10g 及以后版本
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 ;
-
例题
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;
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-1272538/
● 本文博客园地址：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群，学习最实用的数据库技术。
内容总结
以上是互联网集市为您收集整理的Oracle行列互换总结全部内容，希望文章能够帮你解决Oracle行列互换总结所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。