怎样进行oracle的查询结果的行列互换？
全部答案（共1个回答）
在实际使用sql工作中总会碰到将某一列的值放到标题中显示.就是总说的行列转换或者互换.
比如有如下数据:
-- ---------- -------------------- -------
那末我要求显示的结果是:
---------- ---------------------- ----------------------
也就是说把课程这一列放到行上显示.把成绩按照课程分配到相对应的行.
我只介绍2中简单易用的方法,使用游标或者建立临时表的方法就不介绍了.效率很慢,不易理解.
首先建立表:
create table fzq
id varchar
name varchar
kecheng varchar(20),
chengji varchar
--插入数据:
insert into fzq values ('1','a','语文','80');
insert into fzq values('2','a','shuxue','70');
insert into fzq values ('3','b','yuwen','40');
insert into fzq values ('4','b','shuxu','100');
insert into fzq values ('5','c','yuwen','90');
insert into fzq values ('6','c','shuxu','92');
/*首先使用union.如果课程这列有多个值,那么脚本的代码就很长了.*/
select name,sum(yuwen) yuwen,sum(shuxue) shuxue from
select name,chengji yuwen,'0' shuxue from fzq
where kecheng='yuwen' union
select name,'0' yuwen,chengji
where kecheng='shuxue'
/*执行结果:
---------- ---------------------- ----------------------
其次是用case.这种方法代码比较短.适合列值很多的情况.
select name, sum(case kecheng when 'yuwen' then chengji end) yuwen,
sum(case kecheng
when 'shuxue' then chengji
end) shuxue
/*执行结果:
---------- ---------------------- ----------------------
所有例子在oracle中测试,sql server没有测试,请根据实际情况修改
select name,sum(decode(kecheng ,'语文', chengji ,null)) "语文",
sum(decode(kecheng ,'数学', chengji ,null)) "数学",
sum(decode(kecheng ,'英语', chengji ,null)) "英语"
group by name
在实际使用sql工作中总会碰到将某一列的值放到标题中显示.就是总说的行列转换或者互换.
比如有如下数据:
表结构DEPTIDPAREDEPTIDNAMENUMBERNUMBERCHAR (40 Byte)部门id父部门id(所属部门id)部门名称
通过子节点向根节点...
前段时间使用ORACLE，同事提出一个需求，要求将有相同CODE的值连接成一个字符串。这种小问题怎会难倒我，于是提出方案！
建表语句：
很多人都碰到过因为字符集不同而使数据导入失败的情况。这涉及三方面的字符集，一是oracel server端的字符集，二是oracle client端的字符集;三...
很多人都碰到过因为字符集不同而使数据导入失败的情况。这涉及三方面的字符集，一是oracel server端的字符集，二是oracle client端的字符集;三...
一 sql语句的执行步骤1）语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的意义。 2） 语义分析 ，检查语句中涉及的所有数据库对象是否存在，且用户...
答: GetDlgItem(IDC_BUTTON5)返回的是ID为IDC_BUTTON5的一个按钮，的引用(或叫指针)。按钮CButton是继承自windows窗体类...
答: 2010版的..
答: 这特性使它成为理想的软盘和存储卡文件系统，也适合用作不同操作系统中的数据交流
根本就没有正式的国际驾照，如果到国外开车，正式的程序：
1、到公证处办理驾照的公证书，可以要求英文或者法文译本（看看到哪个国家而定）；
2、拿公证书到外交部的领事司指定的地点办理“领事认证”，可以登录外交部网站查询，北京有4、5家代办的，在外交部南街的京华豪园2楼或者中旅都可以。
3、认证后在公证书上面贴一个大标志；
4、有的国家还要到大使馆或者领事馆盖章一下。
偶前几天刚刚办过。
销售额：指企业在销售商品、提供劳务及让渡资产使用权等日常活动中所形成的经济利益的总流入。税法上这一概念是不含任何税金的收入。销售额适用于制造业、商业等。
营业额会计上指的是营业收入，税法指的是应税营业收入。营业额属于含税收入，适用于饮食业、运输业、广告业、娱乐业、建筑安装业等 。
这个问题有点不知所问了。
公务员并不由单位性质决定，行政单位行政编的是公务员，但并不是说行政单位的就是公务员，事业单位里面参照管理的也是公务员。
所以你的问题只能回答为：按公务员管理的是公务员。
如果是下拉的，只有党员而没有预备党员一项，可填党员，但如果是填写的，你就老老实实填预备党员，填成党员对你没什么好处，填预备党员也不会有什么吃亏。
疝气手术也就是腹股沟疝的手术是一种无菌的手术，但是疝气术后一般2天内病人会有吸收热发作，也就是只要是体温在38.°C以内，其实是正常的。但是如果术后三天后还发热，那么可能有伤口感染。
胃肠疾病会给患者朋友们带来很多的困扰，专家介绍说，胃肠疾病的症状主要有腹痛、恶心、呕吐、打嗝、腹泻、胃胀、胃痛、胃酸、厌食、消化不良、饭后饱胀感、胃痉挛等等，大家可以根据这些症状来判断自身疾病。
胃肠疾病是临床上常见的消化道疾病，正是因为这种常见的疾病才往往会被人们所忽视，殊不知，胃肠疾病如果没有得到有效及时的治疗，很有可能会导致胃粘膜的炎症加深，最后甚至是穿孔或者癌变。
除此之外，胃肠疾病还可以影响到其他的一些器官和系统，比如免疫力系统、肠道系统、神经系统等，其中的哪个系统受到影响都会对人体造成多多少少的伤害，因此，胃肠疾病需要尽早发现、及早治疗。
患有胃肠疾病的人在饮食上要多加注意，三餐一定要规律，饮食要清淡，不吃生冷油腻的食物，不吃寒凉食物，更要注意保暖，必要时可以进行中药物调理。
首先由于人们工作的性质不同是很多人长期坐立、站立等使腰部受到损伤，就会导致患者出现腰痛的现象，大多数的患者表现为持续性钝痛、牵扯样痛、腰部痉挛剧痛等症状，久坐症状会更明显。其次出现下肢放射性疼痛的症状，患者平时会感觉到臀部开始疼痛，当咳嗽、打喷嚏或者用力大小便的时候，会感觉到下肢传电般的放射痛就会加重，有时候还会出现腿痛、腰背痛等症状。最后出现麻木、下肢发冷等症状，患者会在小腿或脚面感觉到疼痛，或者由于神经根受到椎间盘突出部分的压迫，引起血流不足，缺血缺氧等麻木现象，病情严重的时候会引起下肢血管壁收缩导致下肢发冷、发凉等症状。患有腰椎病的患者有时候还会出现肌肉瘫痪、神经麻痹、间歇性跛行、脊柱姿势改变等症状，严重影响身体健康，所以发现上述症状后要及时的进行治疗，越早治疗效果会更好。
超过三天的疝气手术还感染的情况，那么需要重视的了，如果是肺部感染。那么一定要治疗。病人会有呼吸粗重和咳嗽的症状出现，需要进一步的检查，然后如果是肺部感染就需要进一步的抗炎的治疗。
在出现颈椎病的时候，或者经常会造成颈部的肌肉出现僵直和盲目，尤其病情比较严重的时候会造成我们出现落枕的症状表现，那么随着疾病的发展，我们会发现我们的神经会受到很大的影响，脊髓会出现受压。
颈椎病的出现是严重的影响着我们的颈部肌肉，这时还会压迫到我们的颈部的神经，我们经常会发现出现头痛或症状，而且两眼也会出现发胀，经常出现恶心呕吐，如果是比较严重的话就会影响到我们猝倒。
颈椎病在早期时会导致我们的肩膀不宽颈肩部出现僵硬，严重的时候会导致我们的政府和上肢出现酸痛，我们平常眩晕行走也会受到很大的影响，在一侧的肩部会表现出非常的沉重感，所以说颈椎病是非常严重的。
我们上面所说的是颈椎病的早期症状表现，通过了解这些早期的症状我们能够及时发现及时预防，在生活中要多锻炼我们的颈部，不要长时间的久坐。
因此在木门的选择时，油漆是必须考虑的因素之一，它直接影响着质感、手感、防潮、环保、耐久、耐黄变等问题。价格因素实木门的价格因其木材用料、纹理等不同而有所差异。市场价格从1500元到3000元不等低档的有松木、杉木等(一般实木复合门就用其做内充材料)中档的有桃花芯、花榉、马兰木、橡胶木等高档的实木有胡桃木、樱桃木、莎比利、红梨木、花梨木等而上等的柚木门一扇(一扇不含套)售价达元。
有啊，我们杰诚水产养殖经销全国，有意可咨询
在所有的多语者中，Michael Levi Harris可能Z好地佐证了这些原理。Harris是一名训练有素的演员，通过模仿，他精通了10种语言，还能基本理解另外12种。虽然是土生土长的纽约人，但他能毫不费力地转换成标准而时髦的英式英语。改变发音的时候，他的姿势都会随着他融入的新角色而变化。“我并没有刻意地去改变我的性格和我的角色。一切就自然而然地发生了，但我也清楚我突然不一样了。”
主要看你多大地方，设备有影院服务器、威动点播影院系统、影院终端、交换机、点片机、App、投影、幕布、音响、功放等，这些是主要的，1间房差不多2万-2万5左右雅马哈的DSP技术可以模拟大厅、教堂等声音效果，在小空间和听音环境不好的场合作用不大。双声道时还有点效果，多声道后相差就更微妙了。选购音箱家用的音响,毕竟咱不是专业的音响高手,如何选购yi台好的音响,就非常重要了.yi般家庭用的音响系统分为音源,功放和发音三个部分.音源建议大家可以选择常见的品牌的碟机,或者比较专业的激光唱机。
体验学习在做中学，在做中乐，在做中熔炼团队，体验分享，相互感恩，创新、激情、人人参与，特别意义!体验式学习年会要请专业的团队来主持和组织，员工和团队是主角，整场年会在创新、创意、竞争、演艺、体验分享中进行!一般，费用节省，组织时间和牵涉人员较少，适合各类的企业内部年会。120人左右的企业，费用一般在1.5万元;150-200人一般在2万元;再多的人数也不会超过3万元。推荐选择，新颖、独特、共荣、共赢!
正在加载...
Copyright &
Corporation, All Rights Reserved
确定举报此问题
举报原因(必选)：
广告或垃圾信息
激进时政或意识形态话题
不雅词句或人身攻击
侵犯他人隐私
其它违法和不良信息
报告，这不是个问题
报告原因(必选)：
这不是个问题
这个问题分类似乎错了
这个不是我熟悉的地区扫一扫体验手机阅读
oracle 行列转换
<span type="1" blog_id="595372" userid='
分享到朋友圈
关注作者，不错过每一篇精彩没有更多推荐了，
加入CSDN，享受更精准的内容推荐，与500万程序员共同成长！没有更多推荐了，
加入CSDN，享受更精准的内容推荐，与500万程序员共同成长！博客分类：
行列转换包括以下六种情况：*列转行*行转列*多列转换成字符串*多行转换成字符串*字符串转换成多列*字符串转换成多行下面分别进行举例介绍。首先声明一点，有些例子需要如下10g及以后才有的知识：a。掌握model子句，b。正则表达式c。加强的层次查询讨论的适用范围只包括8i,9i,10g及以后版本。begin：1、列转行
CREATE TABLE t_col_row(
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);
SELECT * FROM t_col_
CREATE TABLE t_col_row(
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);
SELECT * FROM t_col_
1)UNION ALL适用范围：8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
SELECT id, 'c3' cn, c3 cv FROM t_col_
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
SELECT id, 'c3' cn, c3 cv FROM t_col_
若空行不需要转换，只需加一个where条件，
WHERE COLUMN IS NOT NULL
WHERE COLUMN IS NOT NULL
即可。2)MODEL适用范围：10g及以后
SELECT id, cn, cv FROM t_col_row
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,
SELECT id, cn, cv FROM t_col_row
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,
3)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_
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 TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));
CREATE TYPE cv_varr AS VARRAY(8) OF cv_
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
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
SELECT id, 'c3' cn, c3 cv FROM t_col_
SELECT * FROM t_row_col ORDER BY 1,2;
CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
SELECT id, 'c3' cn, c3 cv FROM t_col_
SELECT * FROM t_row_col ORDER BY 1,2;
1)AGGREGATE FUNCTION适用范围：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;
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等其他聚集函数替代。被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子：
SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;
SELECT mgr,
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"
WHERE mgr IN ()
AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;
SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;
SELECT mgr,
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"
WHERE mgr IN ()
AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;
这里转置列为empno，固定列为mgr，deptno。还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：
--------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
这种情况可以用分析函数实现：
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,
row_number() over(PARTITION BY id ORDER BY cn, cv) rn
FROM t_row_col)
GROUP BY ID;
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,
row_number() over(PARTITION BY id ORDER BY cn, cv) rn
FROM t_row_col)
GROUP BY ID;
2)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,
IN VARCHAR2 DEFAULT NULL,
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,
IN VARCHAR2 DEFAULT NULL,
IN OUT refc);
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS
PROCEDURE p_print_sql(p_txt VARCHAR2) IS
v_len INT;
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));
FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2 IS
v_first INT;
IF p_seq & 1 THEN
RETURN NULL;
IF p_seq = 1 THEN
IF instr(p_str, p_division, 1, p_seq) = 0 THEN
RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
v_first := instr(p_str, p_division, 1, p_seq - 1);
:= 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);
RETURN NULL;
RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
END f_split_
PROCEDURE p_rows_column(p_table
IN VARCHAR2,
p_keep_cols
IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
IN VARCHAR2 DEFAULT NULL,
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_
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_
v_keep_cnt
v_pivot_cnt
v_max_cols
v_partition
VARCHAR2(4000);
v_partition1 VARCHAR2(4000);
v_partition2 VARCHAR2(4000);
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);
FOR j IN 1 .. v_pivot_cnt LOOP
v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
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) || ',';
v_sql := rtrim(v_sql, ',');
EXECUTE IMMEDIATE v_sql
INTO v_max_
v_partition := 'select ';
FOR x IN 1 .. v_keep.COUNT LOOP
v_partition1 := v_partition1 || v_keep(x) || ',';
FOR y IN 1 .. v_pivot.COUNT LOOP
v_partition2 := v_partition2 || v_pivot(y) || ',';
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_
v_partition
:= rtrim(v_partition, ',');
:= 'select ';
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
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 || ',';
IF p_where IS NOT NULL THEN
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
p_where || ') group by ';
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||
') group by ';
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
v_sql := rtrim(v_sql, ',');
p_print_sql(v_sql);
OPEN p_refc FOR v_
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
PROCEDURE p_rows_column_real(p_table
IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
IN VARCHAR2 DEFAULT NULL,
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_
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot_ind_
v_keep_cnt INT;
v_group_by VARCHAR2(2000);
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);
v_sql := 'select ' || 'cast(' || p_pivot_col ||
' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
' group by ' || p_pivot_
EXECUTE IMMEDIATE v_sql BULK COLLECT
FOR i IN 1 .. v_keep.COUNT LOOP
v_group_by := v_group_by || v_keep(i) || ',';
v_group_by := rtrim(v_group_by, ',');
:= '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) || '",';
v_sql := rtrim(v_sql, ',');
IF p_where IS NOT NULL THEN
v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_
p_print_sql(v_sql);
OPEN p_refc FOR v_
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
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,
IN VARCHAR2 DEFAULT NULL,
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,
IN VARCHAR2 DEFAULT NULL,
IN OUT refc);
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS
PROCEDURE p_print_sql(p_txt VARCHAR2) IS
v_len INT;
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));
FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2 IS
v_first INT;
IF p_seq & 1 THEN
RETURN NULL;
IF p_seq = 1 THEN
IF instr(p_str, p_division, 1, p_seq) = 0 THEN
RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
v_first := instr(p_str, p_division, 1, p_seq - 1);
:= 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);
RETURN NULL;
RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
END f_split_
PROCEDURE p_rows_column(p_table
IN VARCHAR2,
p_keep_cols
IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
IN VARCHAR2 DEFAULT NULL,
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_
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_
v_keep_cnt
v_pivot_cnt
v_max_cols
v_partition
VARCHAR2(4000);
v_partition1 VARCHAR2(4000);
v_partition2 VARCHAR2(4000);
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);
FOR j IN 1 .. v_pivot_cnt LOOP
v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
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) || ',';
v_sql := rtrim(v_sql, ',');
EXECUTE IMMEDIATE v_sql
INTO v_max_
v_partition := 'select ';
FOR x IN 1 .. v_keep.COUNT LOOP
v_partition1 := v_partition1 || v_keep(x) || ',';
FOR y IN 1 .. v_pivot.COUNT LOOP
v_partition2 := v_partition2 || v_pivot(y) || ',';
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_
v_partition
:= rtrim(v_partition, ',');
:= 'select ';
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
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 || ',';
IF p_where IS NOT NULL THEN
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
p_where || ') group by ';
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||
') group by ';
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
v_sql := rtrim(v_sql, ',');
p_print_sql(v_sql);
OPEN p_refc FOR v_
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
PROCEDURE p_rows_column_real(p_table
IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
IN VARCHAR2 DEFAULT NULL,
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_
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot_ind_
v_keep_cnt INT;
v_group_by VARCHAR2(2000);
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);
v_sql := 'select ' || 'cast(' || p_pivot_col ||
' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
' group by ' || p_pivot_
EXECUTE IMMEDIATE v_sql BULK COLLECT
FOR i IN 1 .. v_keep.COUNT LOOP
v_group_by := v_group_by || v_keep(i) || ',';
v_group_by := rtrim(v_group_by, ',');
:= '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) || '",';
v_sql := rtrim(v_sql, ',');
IF p_where IS NOT NULL THEN
v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_
p_print_sql(v_sql);
OPEN p_refc FOR v_
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
3.多列转换成字符串
CREATE TABLE t_col_str AS
SELECT * FROM t_col_
CREATE TABLE t_col_str AS
SELECT * FROM t_col_
这个比较简单，用||或concat函数可以实现：
SELECT concat('a','b') FROM
SELECT concat('a','b') FROM
1)|| OR concat适用范围：8i,9i,10g及以后版本
SELECT * FROM t_col_
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_
SELECT * FROM t_col_
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_
4.多行转换成字符串
CREATE TABLE t_row_str(
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');
SELECT * FROM t_row_
CREATE TABLE t_row_str(
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');
SELECT * FROM t_row_
1)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,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;
SELECT id,
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ',' || col, NULL)) ||
MAX(decode(rn, 3, ',' || col, NULL)) str
FROM (SELECT id,
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适用范围：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;
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适用范围：10g及以后版本
SELECT id, substr(str, 2) str FROM t_row_str
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;
SELECT id, substr(str, 2) str FROM t_row_str
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适用范围：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.
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.
适用范围：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
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
5)wmsys.wm_concat适用范围：10g及以后版本这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/')
FROM t_row_str
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/')
FROM t_row_str
5.字符串转换成多列其实际上就是一个字符串拆分的问题。
CREATE TABLE t_str_col AS
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_
SELECT * FROM t_str_
CREATE TABLE t_str_col AS
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_
SELECT * FROM t_str_
1)substr + instr适用范围：8i,9i,10g及以后版本
SELECT id,
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;
SELECT id,
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适用范围：10g及以后版本
SELECT id,
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;
SELECT id,
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.字符串转换成多行
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,
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_
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,
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_
1)UNION ALL适用范围：8i,9i,10g及以后版本
SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
SELECT id,
substr(str,
instr(str || ',', ',', 1, 1) + 1,
instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
SELECT id,
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, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
SELECT id,
substr(str,
instr(str || ',', ',', 1, 1) + 1,
instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
SELECT id,
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
SELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cv
FROM t_str_row
SELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') 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
SELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cv
FROM t_str_row
SELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') AS cv
FROM t_str_row
ORDER BY 1, 2;
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 &=
ORDER BY 1, 2;
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 &=
ORDER BY 1, 2;
3)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;
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,
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 &=
ORDER BY 1, 2;
SELECT t.id,
c.lv AS p,
rtrim(regexp_substr(t.str || ',', '.*?' || ',', 1, c.lv), ',') AS cv
FROM (SELECT id,
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 &=
ORDER BY 1, 2;
4)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;
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适用范围：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;
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适用范围：10g及以后版本
SELECT id, p, cv FROM t_str_row
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY( 0 AS p)
MEASURES( str||',' AS cv)
RULES UPSERT
FROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))
INCREMENT 1
] = rtrim(regexp_substr( cv[0],'.*?'||',',1,cv(p)),','))
ORDER BY 1,2;
SELECT id, p, cv FROM t_str_row
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY( 0 AS p)
MEASURES( str||',' AS cv)
RULES UPSERT
FROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))
INCREMENT 1
] = rtrim(regexp_substr( cv[0],'.*?'||',',1,cv(p)),','))
ORDER BY 1,2;
浏览: 2144 次
来自: 上海
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'