I have a table with below structure:
create table CUSTOMER_INFO
(
cust_id NUMBER,
cust_name VARCHAR2(50),
cust_address VARCHAR2(50)
)
Example Data :
cust_id | cust_name | cust_address
------------------------------------------
1 | A | Tehran,Tehran
2 | B | Rasht,Tehran
3 | C | Minsk,Tehran
I want to have this output:
cust_id | cust_name | cust_address
------------------------------------------
1 | A | Tehran
2 | B | Tehran
2 | B | Rasht
3 | C | Minsk
3 | C | Tehran
what I've written so far is this :
select distinct cust_id,
cust_name,
address
from (select distinct cust_id,
cust_name,
regexp_substr(cust_address, '[^,]+', 1, level) address
from customer_info
connect by regexp_substr(cust_address, '[^,]+', 1, level) is not null)
order by cust_id
I want to know if there is better way of writing this query and Will this solution produce false result ?
Thanks in advance
Best Answer
Such an approach makes sure you don't get too many duplicates as your query does (so you removed them with a
distinct
in line #4; without it, you'd get 12 rows as a result):However, as you want to have only one
1-A-Tehran
combination,distinct
in line #6 has to be uncommented.