0

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

  • 1
    Please explain your logic. In your example, all of your cust-id have two values in cust_address. You show that you want to return one of those for cust_id=1, but both for the other cust_id. That said, your data model is fundamentally broken. Those comma-separated values in a single column mean you don't even meet First Normal Form. Get your data model correct (Third Normal Form) and your problem disappears. – EdStevens Aug 29 '20 at 11:46
  • Cust_id=1 has only one unique Address which is 'Tehran' so I need to have one record which is cust_id=1 , cust_address='Tehran'. Cust_id=2 has two unique Addresses which are 'Rasht' and 'Tehran' so I need to have two records for cust_id=2. The point is that there might be more than two unique addresses for each cust_id for instance there might be cust_id=10 with address column "Tehran,Minsk,Rasht" and now , I need to have three records for this cust_id.this table has been given to us and we were not in charge of modeling the data unfortunately. – Pantea Aug 29 '20 at 11:53
2

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):

SQL> with customer_info (cust_id, cust_name, cust_address) as
  2    (select 1, 'A', 'Tehran,Tehran' from dual union all
  3     select 2, 'B', 'Rasht,Tehran'  from dual union all
  4     select 3, 'C', 'Minsk,Tehran'  from dual
  5    )
  6  select --distinct      --> use DISTINCT to get result you want
  7         cust_id,
  8         cust_name,
  9         regexp_substr(cust_address, '[^,]+', 1, column_value) cust_address
 10  from customer_info cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level <= regexp_count(cust_address, ',') + 1
 13                       ) as sys.odcinumberlist))
 14  order by cust_id;

   CUST_ID C CUST_ADDRESS
---------- - ----------------------------------------------------
         1 A Tehran
         1 A Tehran
         2 B Rasht
         2 B Tehran
         3 C Minsk
         3 C Tehran

6 rows selected.

SQL>

However, as you want to have only one 1-A-Tehran combination, distinct in line #6 has to be uncommented.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.