converting a column result in to multiple row in oracle

Question

I have material_info table in oracle db

MATERIAL    COUNTRIES
----------------------------
HX345TY     US,IN,SG,CN,JP
MXT15WO     SL,AU,IN,US,AF
UIY7890#RT  UK,US,IN,SG,PK

i want output as below

MATERIAL    COUNTRIES
-----------------------------
HX345TY     US
HX345TY     IN
HX345TY     SG
HX345TY     CN
HX345TY     JP
MXT15WO     SL
MXT15WO     AU
MXT15WO     IN
MXT15WO     US
MXT15WO     AF
UIY7890#RT  UK
UIY7890#RT  US
UIY7890#RT  IN
UIY7890#RT  SG
UIY7890#RT  PK

I wrote the query for this like below. but it is not working. Can anyone give the solutions

select material, trim(regexp_substr(COUNTRIES,'[^,]+', 1, level) ) COUNTRIES, level
from material_info connect by regexp_substr(COUNTRIES, '[^,]+', 1, level) is not null;

You shouldn't be storing comma separated values in a single column to begin with — , Apr 24 '19 at 08:31

score 2 · Accepted Answer · answered Apr 24 '19 at 07:25

2

You should prefer prior + sys_guid technique. It is far efficient than distinct for large number of records.

select material,
       regexp_substr(countries, '[^,]+', 1, level) countries
  from material_info
connect by level <= regexp_count(countries, '[^,]+')
and prior material = material
and prior sys_guid() is not null;

DEMO

answered Apr 24 '19 at 07:25

Kaushik Nayak

30,772
5
32
45

if i want to conditionally filter the query with material then what will be the query. let say i want to filter with 'material' = 'UIY7890#RT' – Sangram Badi Apr 24 '19 at 08:31
Why should "you prefer" this technique? There are [faster methods that do not use regular expressions](https://stackoverflow.com/a/38372145/1509264). – MT0 Apr 24 '19 at 08:31

Barbaros Özhan · Answer 2 · 2019-04-24T07:21:50.923

You can slightly change by using distinct, and regexp_count to make dynamic

select distinct material,
       regexp_substr(countries, '[^,]+', 1, level) countries
  from material_info
connect by level <= regexp_count(countries, '[^,]+')
order by material;

or use group by material, countries, level as

select material,
       regexp_substr(countries, '[^,]+', 1, level) countries
  from material_info
connect by level <= regexp_count(countries, '[^,]+')
group by material, countries, level;

even no need to use trim

Demo

converting a column result in to multiple row in oracle

2 Answers2