How to solve this query...I tried but not gettin output-CodePudding

Name    Flag
Aman    A,B
Rajesh  C,D


o/p--> Aman  A
       Aman  B
       Rajesh  C
       Rajesh  D


select name,substr(replace(FLAG,',',null),1,level) from w1
where connect by level <= length(replace(flag,',', null)',');

CodePudding user response：

One option, with sample data

SQL> with test (name, flag) as
  2    (select 'Aman'  , 'A,B'   from dual union all
  3     select 'Rajesh', 'C,D,E' from dual
  4    )

is to split flag at the comma character, up to max number of elements split by comma:

  5  select name,
  6    regexp_substr(flag, '[^,] ', 1, column_value) flag
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(flag, ',')   1
 10                       ) as sys.odcinumberlist))
 11  order by name, flag;

NAME   FLAG
------ --------------------
Aman   A
Aman   B
Rajesh C
Rajesh D
Rajesh E

SQL>

CodePudding user response：

Does this help you?

WITH
    tbl (A_NAME, FLAGS) AS
        (
            Select 'Aman', 'A,B' From Dual Union All
            Select 'Rajesh', 'C,D' From Dual
        )
Select A_NAME, SubStr(FLAGS, 1, InStr(FLAGS, ',', 1, 1) - 1) "FLAG" From tbl Union All
Select A_NAME, SubStr(FLAGS, InStr(FLAGS, ',', 1, 1)   1) "FLAG" From tbl 
Order By A_NAME, FLAG

A_NAME FLAG
------ ----
Aman   A    
Aman   B    
Rajesh C    
Rajesh D