converting a column result in to multiple row in oracle [duplicate]

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 — user330315
– user330315, Commented Apr 24, 2019 at 8:31

Kaushik Nayak · Accepted Answer · 2019-04-24 07:25:38Z

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, 2019 at 7:25

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sangram Badi Over a year ago

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'

MT0 Over a year ago

Why should "you prefer" this technique? There are faster methods that do not use regular expressions.

Barbaros Özhan · Accepted Answer · 2019-04-24 07:21:50Z

0

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

edited Apr 24, 2019 at 7:21

answered Apr 24, 2019 at 7:10

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

converting a column result in to multiple row in oracle [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related