求SQL：一个字段转变成一行

Question

表中的一个字段varchar2型的，字段中的值有逗号的表示关联多个参数，要求重新生存一条独立的记录，数据库中有很多这样的记录，逗号数量不确定[0-10],一个表内操作要保留其他参数
如
姓名               班级
-------------------------
张三，李四，王五   0101
改成
姓名 班级
-----------
张三  0101
李四  0101
王五  0101
……

Answer 1

SQL> select * from t_student;
 
NAME                                                                             CLASS
-------------------------------------------------------------------------------- ----------
张三,李四,王五                                                                   0101
 
SQL> 
SQL> SELECT substr(',' || a.NAME || ',',
  2                instr(',' || a.NAME || ',', ',', 1, b.rn) + 1,
  3                instr(',' || a.NAME || ',', ',', 1, b.rn + 1) -
  4                instr(',' || a.NAME || ',', ',', 1, b.rn) - 1) NAME,
  5         a.CLASS
  6    FROM t_student a,
  7         (SELECT rownum rn
  8            FROM dual
  9          CONNECT BY rownum <=
 10                     (SELECT MAX(length(translate(NAME, ',' || NAME, ','))) + 1
 11                        FROM t_student)) b
 12   WHERE substr(',' || a.NAME || ',',
 13                instr(',' || a.NAME || ',', ',', 1, b.rn) + 1,
 14                instr(',' || a.NAME || ',', ',', 1, b.rn + 1) -
 15                instr(',' || a.NAME || ',', ',', 1, b.rn) - 1) IS NOT NULL;
 
NAME                                                                             CLASS
-------------------------------------------------------------------------------- ----------
张三                                                                             0101
李四                                                                             0101
王五                                                                             0101
 
SQL> 

Answer 2

参考下这个。。5l的好像如果对于有多个行就有问题了。。SQL> 
SQL> with classes as
  2  (
  3  select  '张三,李四,王五' name,'0101' grade  from dual
  4  union all
  5  select  '刘三,武四,赵五' name,'0102' grade  from dual
  6  )
  7  select regexp_substr(name, '[^,]+', 1, lv), grade
  8    from (select distinct name, grade, level lv
  9            from (select name,
 10                         grade,
 11                         length(name) - length(replace(name, ',', '')) + 1 rn
 12                    from classes)
 13          connect by level <= rn
 14           order by grade)
 15  ;REGEXP_SUBSTR(NAME,'[^,]+',1,L GRADE
------------------------------ -----
张三                           0101
李四                           0101
王五                           0101
刘三                           0102
武四                           0102
赵五                           01026 rows selectedSQL

Answer 3

SQL> with t_student as
  2      (
  3      select  '张三,李四,王五' name,'0101' CLASS  from dual
  4      union all
  5      select  '刘三,武四,赵五' name,'0102' CLASS  from dual
  6      )
  7  SELECT substr(',' || a.NAME || ',',
  8                   instr(',' || a.NAME || ',', ',', 1, b.rn) + 1,
  9                   instr(',' || a.NAME || ',', ',', 1, b.rn + 1) -
 10                   instr(',' || a.NAME || ',', ',', 1, b.rn) - 1) NAME,
 11            a.CLASS
 12       FROM t_student a,
 13            (SELECT rownum rn
 14               FROM dual
 15             CONNECT BY rownum <=
 16                        (SELECT MAX(length(translate(NAME, ',' || NAME, ','))) + 1
 17                           FROM t_student)) b
 18      WHERE substr(',' || a.NAME || ',',
 19                   instr(',' || a.NAME || ',', ',', 1, b.rn) + 1,
 20                   instr(',' || a.NAME || ',', ',', 1, b.rn + 1) -
 21                   instr(',' || a.NAME || ',', ',', 1, b.rn) - 1) IS NOT NULL;
 
NAME                             CLASS
-------------------------------- -----
张三                             0101
刘三                             0102
李四                             0101
武四                             0102
王五                             0101
赵五                             0102
 
6 rows selected
 
SQL> 没问题的,放心使用.

Answer 4

--简化下：
with tab as(
select  '张三,李四,王五' name,'0101' CLASS  from dual
union all
select  '刘三,武四,赵五' name,'0102' CLASS  from dual
)
select distinct substr(','||name||',',instr(','||name||',',',',1,level)+1,
          instr(','||name||',',',',1,level+1)-instr(','||name||',',',',1,level)-1) name ,
       class
from tab
connect by
level <= length(','||name||',') - length(replace(','||name||',', ',', ''))-1
NAME   CLASS
------ -----
刘三    0102
张三    0101
李四    0101
武四    0102
王五    0101
赵五    0102
 

Answer 5

-----------承蒙各位意思可以更加简化下。。SQL> with classes as
  2   (
  3    select  '张三,李四,王五' name,'0101' grade  from dual
  4    union all
  5     select  '刘三,武四,赵五' name,'0102' grade  from dual
  6  )
  7  select regexp_substr(name, '[^,]+', 1, lv), grade
  8    from (select distinct name, grade, level lv
  9            from classes
 10          connect by level <=
 11                     length(name) - length(replace(name, ',', '')) + 1
 12           order by grade)
 13  ;REGEXP_SUBSTR(NAME,'[^,]+',1,L GRADE
------------------------------ -----
张三                           0101
李四                           0101
王五                           0101
刘三                           0102
武四                           0102
赵五                           01026 rows selectedSQL>

Answer 6

如果逗号的个数不多，可以用substr和instr一个一个获取
如果很多的话，就要用正则，但是我不了解正则，感觉很麻烦

Answer 7

多谢大家顶力支持
  并回复13楼，正则式冒问题，只是感觉好复杂，我的操作对像是几个这样的表要update

Answer 8

--1.
select name,grade 
  from(
      with tmp as(
      select  '张三,李四,王五' name,'0101' grade  from dual
      union all
      select  '刘三,武四,赵五','0102' from dual
      )
      select substr(','||name||',',
                     instr(','||name||',',',',1,lev)+1,
                     instr(','||name||',',',',1,lev+1)-instr(','||name||',',',',1,lev)-1
                   ) name,
             grade
        from tmp,
             (select level lev
                from dual
               connect by level <= (select max(length(name)-length(replace(name,',','')))+1 from tmp)
             )
        )
  where name is not null;或:
select distinct name,grade 
  from(
      with tmp as(
      select  '张三,李四,王五' name,'0101' grade  from dual
      union all
      select  '刘三,武四,赵五','0102' from dual
      )
      select distinct substr(','||name||',',
                     instr(','||name||',',',',1,lev)+1,
                     instr(','||name||',',',',1,lev+1)-instr(','||name||',',',',1,lev)-1
                   ) name,
             grade
        from (select level lev,name,grade
                from tmp
               connect by level <= (select max(length(name)-length(replace(name,',','')))+1 from tmp)
             )
        )
  where name is not null;或:
with tmp as(
select  '张三,李四,王五' name,'0101' grade  from dual
union all
select  '刘三,武四,赵五','0102' from dual
)
select distinct substr(','||name||',',
               instr(','||name||',',',',1,lev)+1,
               instr(','||name||',',',',1,lev+1)-instr(','||name||',',',',1,lev)-1
             ) name,
       grade
  from (select level lev,name,grade
          from tmp
         connect by level <= length(name)-length(replace(name,',',''))+1
       )
where substr(','||name||',',
               instr(','||name||',',',',1,lev)+1,
               instr(','||name||',',',',1,lev+1)-instr(','||name||',',',',1,lev)-1
             )  is not null 
    order by grade;
  
--2.
with tmp as(
select  '张三,李四,王五' name,'0101' grade  from dual
union all
select  '刘三,武四,赵五','0102' from dual
)
select substr(',' || a.name || ',',
               instr(',' || a.name || ',', ',', 1, b.rn) + 1,
               instr(',' || a.name || ',', ',', 1, b.rn + 1) -
               instr(',' || a.name || ',', ',', 1, b.rn) - 1) name,
          a.grade
     from tmp a,
          (select rownum rn
              from dual
           connect by rownum <= (select max(length(translate(name, ',' || name, ','))) + 1 from tmp)) b
    where substr(',' || a.name || ',',
                instr(',' || a.name || ',', ',', 1, b.rn) + 1,
                instr(',' || a.name || ',', ',', 1, b.rn + 1) -
                instr(',' || a.name || ',', ',', 1, b.rn) - 1) is not null;
                --3.10g之后适用正则表达式
with classes as
(
select  '张三,李四,王五' name,'0101' grade  from dual
union all
select  '刘三,武四,赵五' name,'0102' grade  from dual
)
select regexp_substr(name, '[^,]+', 1, lv), grade
  from (select distinct name, grade, level lv
          from (select name,
                       grade,
                       length(name) - length(replace(name, ',', '')) + 1 rn
                  from classes)
        connect by level <= rn
         order by grade
        );