Oralce 按分隔符把一列转成多行

1.前言最近因项目需求，需要把员工的工作组返回给前台，但是数据库是把员工的工作组Id,都存在一个字段内了（以“逗号”分隔），而这样不符合前台的需要，他们需要一行，一行的数据。如：数据库：userId, workgroup1001 10,12,1510022,4,5 前台需要： userId, workgroup1001 101001 121001 151002 21002 41002 5 2. 分析思路：大体的思路是这样的：首先：要知道，每一员工最多有多少个组。其次：建一个有关“数”的临时表，与上面的组数进行关联，这样就出现了“多”行最后：多“行”有了，剩下的就是对每一行的组进行刷选。如第一行取第一个逗号左边的，第二行取第二个逗号左边的, 依此类推。 3. 实现：根据上次的思路，来实现：第一步： with v_usergroups as （select "1001" as userId,"10,12,15" as workgroups from dualunionselect "1002" as userId,"2,4,5" as workgroups from dual）select userid,"," || workgroups ||"," AS tempgroups,length（workgroups || ","） - nvl（length（REPLACE（workgroups, ","））， 0） AS groupcount FROM v_usergroups PS: 这里在"workgroup" 的前后也加了逗号，是为了后面使用方面。第二步：select LEVEL lv from dual CONNECT BY LEVEL <= 5 PS:这里的5，我们是根据业务需要，每一员工最多分为5个组，当然也可以写其他的值，但一定要大于第一步求得的"groupcount". 到这里后，我们对这两个表进行关联，看看值怎么样： with v_usergroups as （select "1001" as userId,"10,12,15" as workgroups from dualunionselect "1002" as userId,"2,4,5" as workgroups from dual）select * from （select userid,"," || workgroups ||"," AS tempgroups,length（workgroups || ","） - nvl（length（REPLACE（workgroups, ","））， 0） AS groupcount FROM v_usergroups ） a,（select LEVEL lv from dual CONNECT BY LEVEL <= 5） b where b.lv<=a.groupcount order by userid,lv USERIDTEMPGROUPSGROUPCOUNTLV11001,10,12,15,3121001,10,12,15,3231001,10,12,15,3341002,2,4,5,3151002,2,4,5,3261002,2,4,5,33 到这里，就离我们最终的结果很近了。只需要在外层对"tempgroups"做一下简单的处理就可以了：第三步：这一步的主要思路就是：截串。第一个组应该是第一逗号和第二个逗号之间的值，第二个组应该是第二个逗号与第三个逗号之间的值，那第一个，和第二个如何表示呢，其实就是利用字段lv。也就是： substr（tempgroups,instr（tempgroups, ",", 1, lv） + 1,instr（tempgroups, ",", 1, lv + 1） - （instr（tempgroups, ",", 1, lv） + 1））最后的SQL 如下： with v_usergroups as （select "1001" as userId,"10,12,15" as workgroups from dualunionselect "1002" as userId,"2,4,5" as workgroups from dual）select userid,substr（tempgroups,instr（tempgroups, ",", 1, lv） + 1,instr（tempgroups, ",", 1, lv + 1） - （instr（tempgroups, ",", 1, lv） + 1）） from （select userid,"," || workgroups ||"," AS tempgroups,length（workgroups || ","） - nvl（length（REPLACE（workgroups, ","））， 0） AS groupcount FROM v_usergroups ） a,（select LEVEL lv from dual CONNECT BY LEVEL <= 5） b where b.lv<=a.groupcount order by userid,lv更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle 索引简单总结安装Linux X86-64的Oracle10201出现链接ins_ctx.mk错误相关资讯 Oralce列转行本文评论查看全部评论（0）