查看:4568|回复：0
初级工程师
当你在开发会员系统时，一定会遇到在一个字段中，用逗号或其他符号分隔存储多个信息的情况。例如：保存某个用户的一对多权限时，在权限组这个字段中，就会以逗号分隔保存多个权限编号。
& & 那么，现在假设给出某一个权限编号，要去检索出包含这个权限的所有用户记录集合，该怎么去写这个sql语句呢？分析：这时就需要在逗号分隔的多个权限编号字段中去匹配给出的这个权限编号。如果使用like去做，一则效率不高，二则匹配不精确。因此用到了逗号分隔列转行的方法。目前该方法只适合在Oracle数据库中使用。该方法只需要sql语句就可以实现列转行。
& & 下面给出该方法的示例：
& & select a,b,c from
& & （with test as （select 'aaa' a,'bbb' b,'1,2,3' c from dual）
& & select a,b,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 c
& & from （select a,b,',' || c || ',' AS ca,length（c || ','） - nvl（length（REPLACE（c, ','））， 0） AS cnt FROM test） t,
& & （select LEVEL lv from dual CONNECT BY LEVEL &= 100） c where c.lv &= t.cnt ）
& & 执行以上代码，结果如下图：
(17.08 KB)
& & 模拟数据列 c 中的内容&1,2,3&被转成了三行，前面两列的内容同时附带过去。在实际运用时，只要把
& & select 'aaa' a,'bbb' b,'1,2,3' c from dual 替换成实际需要转换的数据表中的字段即可，其中c字段必须是要进行转换的保存逗号分隔内容的字段。下面的内容都可以不需要更改。其中&LEVEL &= 100&中的数字100代表匹配字段内容中出现逗号的次数，可以自行更改。SQL代码 列转行
REGEXP_SUBSTR(a.rolecode ,'[^,]+',1,l)
select 'a,aa,aaa' rolecode from dual
(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL&=100) b
WHERE l &=LENGTH(a.rolecode) - LENGTH(REPLACE(rolecode,','))+1
with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum &= length(regexp_replace(id,'[^,]+'))
SQL代码 行转列
select name,coures,to_char(wmsys.wm_concat(xxx.score)) c
(select '小明' name,'语文' coures,90 score
select '小明' name,'语文' coures,91 score
select '小明' name,'数学' coures,90 score
select '小明' name,'数学' coures,91 score
from dual) xxx
group by xxx.name,coures
阅读(...) 评论()我是程序员、我乐意、我高兴
[DB][ORACLE]逗号分隔列转行的实现方法
逗号分隔列转行的实现方法
1、转换方法如下所示：
CREATE OR REPLACE VIEW V_CON_PROJECT AS
SELECT cp."CON_NUM",
cp."PROJECT_CODE",
cp."REQ_MONEY",
cp."IS_HAS_BUILD_DEPT",
cp."BO_CON_PROJECT_ID",
REGEXP_SUBSTR(CP.MIS_NUM, '[^,]+', 1, L) AS MIS_NUM
FROM BO_CON_PROJECT CP,
(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL &= 1000)
WHERE L(+) &= LENGTH(CP.MIS_NUM) - LENGTH(REPLACE(CP.MIS_NUM, ',')) + 1
1）、表BO_CON_PROJECT中、MIS_NUM字段是以逗号分隔的数据（如：03）
2）、上述SQL中WHERE条件中的(+)必须加上、否则MIS_NUM字段为空的数据无法取得、而且加上(+)后、SQL执行效率提高几个数量级。测试数据如下图所示：
没有更多推荐了，
加入CSDN，享受更精准的内容推荐，与500万程序员共同成长！oracle列转行_百度文库
您的浏览器Javascript被禁用，需开启后体验完整功能，
赠送免券下载特权
10W篇文档免费专享
部分付费文档8折起
每天抽奖多种福利
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
oracle列转行
&&oracle列转行
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，同时保存到云知识，更方便管理
加入VIP
还剩7页未读，
定制HR最喜欢的简历
你可能喜欢Oracle行转列、列转行的Sql语句总结
多行转字符串
这个比较简单，用||或concat函数可以实现
select concat(id,username) str from app_user
select id||username str from app_user
字符串转多列
实际上就是拆分字符串的问题，可以使用 substr、instr、regexp_substr函数方式
字符串转多行
使用union all函数等方式
wm_concat函数
首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以&,&号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据
create table test(id number,name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');
效果1 : 行转列 ，默认逗号隔开
select wm_concat(name)
效果2: 把结果里的逗号替换成&|&
select replace(wm_concat(name),',','|')
效果3: 按ID分组合并name
select id,wm_concat(name) name f
sql语句等同于下面的sql语句
-------- 适用范围：8i,9i,10g及以后版本
（ MAX + DECODE ）
select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ',' || name, null)) || max(decode(rn, 3, ',' || name, null)) str
from (select id,name,row_number() over(partition by id order by name) as rn from test) t group by id order by 1;
-------- 适用范围：8i,9i,10g及以后版本 （ ROW_NUMBER + LEAD ）
select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1) over(partition by id order by name) ||
lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3) over(partition by id order by name) as str from test) where rn = 1 order by 1;
-------- 适用范围：10g及以后版本 （ MODEL ）
select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number() over(partition by id order by name) as rn)
measures (cast(name 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;
-------- 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）
select t.id id,max(substr(sys_connect_by_path(t.name,','),2)) str from (select id, name, row_number() over(partition by id order by name) rn from test) t
start with rn = 1 connect by rn = prior rn + 1 and id = prior id group by t.&/span&
懒人扩展用法:
案例: 我要写一个视图,类似&create or replace view as select 字段1,...字段50 from tablename& ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单，假设我的APP_USER表中有（id,username,password,age）4个字段。查询结果如下
/** 这里的表名默认区分大小写 */
select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sqlStr from user_tab_columns where table_name='APP_USER';
利用表方式查询
select * from user_tab_columns
11g 行列互换 pivot 和 unpivot 说明
在Oracle 11g中，Oracle 又增加了2个查询：pivot（行转列） 和unpivot（列转行）
参考：https://blog.csdn.net/tianlesoftware/article/details/7060306、https://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
google 一下，网上有一篇比较详细的文档：https://www.oracle-developer.net/display.php?id=506
pivot 列转行
测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。
create table demo(id int,name varchar(20),nums int);
---- 创建表
insert into demo values(1, '苹果', 1000);
insert into demo values(2, '苹果', 2000);
insert into demo values(3, '苹果', 4000);
insert into demo values(4, '橘子', 5000);
insert into demo values(5, '橘子', 3000);
insert into demo values(6, '葡萄', 3500);
insert into demo values(7, '芒果', 4200);
insert into demo values(8, '芒果', 5500);
分组查询 （当然这是不符合查询一条数据的要求的）
select name, sum(nums) nums from demo group by name
行转列查询
select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));
注意： pivot（聚合函数 for 列名 in（类型）） ，其中 in(&&) 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers
当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解
select * from （select sum(nums) 苹果 from demo where name='苹果'）,（select sum(nums) 橘子 from demo where name='橘子'）,
（select sum(nums) 葡萄 from demo where name='葡萄'）,（select sum(nums) 芒果 from demo where name='芒果'）;
unpivot 行转列
顾名思义就是将多列转换成1列中去
案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。
创建表和数据
create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
insert into Fruit values(1,'苹果',00,5000);
insert into Fruit values(2,'橘子',00,1500);
insert into Fruit values(3,'香蕉',00,2500);
insert into Fruit values(4,'葡萄',00,3500);
select * from Fruit
列转行查询
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )
注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量
同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高
select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f
select id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f
select id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f
select id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f
上述pivot列转行示例中，你已经知道了需要查询的类型有哪些，用in()的方式包含，假设如果您不知道都有哪些值，您怎么构建查询呢？
pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出，在此输出中，您可以指定一个特殊的子句 ANY 而非文字值
示例如下：
select * from (
select name, nums as &Purchase Frequency&
from demo t
pivot xml (
sum(nums) for name in (any)
如您所见，列 NAME_XML 是 XMLTYPE，其中根元素是 &PivotSet&。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。
Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表，而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样，您可以使用 unpivot 操作转换任何交叉表报表，以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出，您不必指定 pivot 操作需要搜索的值域。