Oracle中字符串单行“，”分割成多行

--用逗号分割单个字符串;
--示例：
select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i'), names
  from (select '1,2,3' nums, 'denggh' names from dual) q
connect by rownum <=
           LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1;

 

--用逗号分割变量
--示例：
select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') as nums_adno
  from (select adno nums from IB_CNT_ADPLAYRULE_INFO where adno like '%,%') q
connect by rownum <=
           LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1

 

--用逗号分割一行变量字段，查询数据
select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
       PLAYRULEID,
       DEVICENO,
       ADNO,
       PLAYSTYLE,
       WORDSTYLE,
       PICTURESTYLE,
       VOICESTYLE,
       VIDEOSTYLE,
       FILESPACETIME,
       ADSPACETIME,
       HOMEPLAYFLAG,
       HOMESPACETIME,
       DEFAULTFLAG,
       REMARK,
       DEVICEMODEL,
       DEVICETYPE
  from (select adno nums,
               PLAYRULEID,
               DEVICENO,
               ADNO,
               PLAYSTYLE,
               WORDSTYLE,
               PICTURESTYLE,
               VOICESTYLE,
               VIDEOSTYLE,
               FILESPACETIME,
               ADSPACETIME,
               HOMEPLAYFLAG,
               HOMESPACETIME,
               DEFAULTFLAG,
               REMARK,
               DEVICEMODEL,
               DEVICETYPE
          from IB_CNT_ADPLAYRULE_INFO
         where adno like '%,%') q
connect by rownum <=
           LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1

 

 

 

--使用with as 方法（与union all错误）

 

with a as
 (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
         PLAYRULEID,
         DEVICENO,
         ADNO,
         PLAYSTYLE,
         WORDSTYLE,
         PICTURESTYLE,
         VOICESTYLE,
         VIDEOSTYLE,
         FILESPACETIME,
         ADSPACETIME,
         HOMEPLAYFLAG,
         HOMESPACETIME,
         DEFAULTFLAG,
         REMARK,
         DEVICEMODEL,
         DEVICETYPE
    from (select adno nums,
                 PLAYRULEID,
                 DEVICENO,
                 ADNO,
                 PLAYSTYLE,
                 WORDSTYLE,
                 PICTURESTYLE,
                 VOICESTYLE,
                 VIDEOSTYLE,
                 FILESPACETIME,
                 ADSPACETIME,
                 HOMEPLAYFLAG,
                 HOMESPACETIME,
                 DEFAULTFLAG,
                 REMARK,
                 DEVICEMODEL,
                 DEVICETYPE
            from IB_CNT_ADPLAYRULE_INFO
           where adno like '%,%') q
  connect by rownum <=
             LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1)

 

SELECT a.PLAYRULEID,
       a.DEVICENO,
       a.ADNO,
       a.PLAYSTYLE,
       a.WORDSTYLE,
       a.PICTURESTYLE,
       a.VOICESTYLE,
       a.VIDEOSTYLE,
       a.FILESPACETIME,
       a.ADSPACETIME,
       a.HOMEPLAYFLAG,
       a.HOMESPACETIME,
       a.DEFAULTFLAG,
       a.REMARK,
       a.DEVICEMODEL,
       a.DEVICETYPE,
       b.ADNAME,
       b.ADCONTENT,
       b.ADTYPE
  FROM a, IB_CNT_AD_INFO b
 where a.nums in (b.adno)

 

union

 

with c as (SELECT wmsys.wm_concat(d.ADCONTENT) ADCONTENT
             FROM (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums
                     from (select adno nums
                             from IB_CNT_ADPLAYRULE_INFO
                            where adno like '%,%') q
                   connect by rownum <=
                              LENGTH(q.nums) -
                              LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) a,
                  IB_CNT_AD_INFO d
            where a.nums in (d.adno))
select REPLACE(c.ADCONTENT, ',', '') from c

 

--使用嵌套方法进行查询
select n.PLAYRULEID,
       n.DEVICENO,
       n.ADNO,
       n.PLAYSTYLE,
       n.WORDSTYLE,
       n.PICTURESTYLE,
       n.VOICESTYLE,
       n.VIDEOSTYLE,
       n.FILESPACETIME,
       n.ADSPACETIME,
       n.HOMEPLAYFLAG,
       n.HOMESPACETIME,
       n.DEFAULTFLAG,
       n.REMARK,
       n.DEVICEMODEL,
       n.DEVICETYPE,
       n.ADNAME,
       m.ADCONTENT,
       n.ADTYPE
  from (select REPLACE(c.ADCONTENT, ',', '') as ADCONTENT,
               c.playruleid as c_playruleid
          from (SELECT playruleid, wmsys.wm_concat(d.ADCONTENT) ADCONTENT
                  FROM (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
                               playruleid
                          from (select adno nums, playruleid
                                  from IB_CNT_ADPLAYRULE_INFO
                                 where adno like '%,%') q
                        connect by rownum <=
                                   LENGTH(q.nums) -
                                   LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) a,
                       IB_CNT_AD_INFO d
                 where a.nums in (d.adno)
                 group by playruleid) c) m,
      
       (SELECT a.PLAYRULEID,
               a.DEVICENO,
               a.ADNO,
               a.PLAYSTYLE,
               a.WORDSTYLE,
               a.PICTURESTYLE,
               a.VOICESTYLE,
               a.VIDEOSTYLE,
               a.FILESPACETIME,
               a.ADSPACETIME,
               a.HOMEPLAYFLAG,
               a.HOMESPACETIME,
               a.DEFAULTFLAG,
               a.REMARK,
               a.DEVICEMODEL,
               a.DEVICETYPE,
               b.ADNAME,
               b.ADCONTENT,
               b.ADTYPE
          FROM (select regexp_substr(q.nums, '[^,]+', 1, rownum, 'i') nums,
                       PLAYRULEID,
                       DEVICENO,
                       ADNO,
                       PLAYSTYLE,
                       WORDSTYLE,
                       PICTURESTYLE,
                       VOICESTYLE,
                       VIDEOSTYLE,
                       FILESPACETIME,
                       ADSPACETIME,
                       HOMEPLAYFLAG,
                       HOMESPACETIME,
                       DEFAULTFLAG,
                       REMARK,
                       DEVICEMODEL,
                       DEVICETYPE
                  from (select adno nums,
                               PLAYRULEID,
                               DEVICENO,
                               ADNO,
                               PLAYSTYLE,
                               WORDSTYLE,
                               PICTURESTYLE,
                               VOICESTYLE,
                               VIDEOSTYLE,
                               FILESPACETIME,
                               ADSPACETIME,
                               HOMEPLAYFLAG,
                               HOMESPACETIME,
                               DEFAULTFLAG,
                               REMARK,
                               DEVICEMODEL,
                               DEVICETYPE
                          from IB_CNT_ADPLAYRULE_INFO
                         where adno like '%,%') q
                connect by rownum <=
                           LENGTH(q.nums) -
                           LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) a,
               IB_CNT_AD_INFO b
         where a.nums in (b.adno)
         order by b.ADNO) n
 
--多行数据进行合并：oracle 10g以上 一个wm_concat
select id,wm_concat(name) name from test group by id;

参考博客：
https://blog.csdn.net/seandba/article/details/72669074
https://blog.csdn.net/u014514528/article/details/80611128  
https://www.cnblogs.com/MoMo1002/p/6594832.html
https://www.cnblogs.com/xieon1986/archive/2013/01/16/2863077.html