CODEWITHSUNDEEP
oracle-databaseplsql
Learner
Learner

Reputation: 23

Fetch name based on comma-separated ids

I have two tables, customers and products.

products:

productid name
 1        pro1
 2        pro2
 3        pro3

customers:

id    name   productid
1     cust1  1,2
2     cust2  1,3
3     cust3

i want following result in select statement,

id   name    productid
 1  cust1    pro1,pro2
 2  cust2    pro1,pro3
 3  cust3

i have 300+ records in both tables, i am beginner to back end coding, any help?

Upvotes: 0

Views: 71

Answers (1)

XING
XING

Reputation: 9886

Definitely a poor database design but the bad thing is that you have to live with that. Here is a solution which I created using recursive query. I don't see the use of product table though since your requirement has nothing to do with product table.

with 
    --Expanding each row seperated by comma     
    tab(col1,col2,col3) as (                           
                             Select distinct c.id,c.prdname,regexp_substr(c.productid,'[^,]',1,level)
                              from customers c
                              connect by regexp_substr(c.productid,'[^,]',1,level) is not null
                              order by 1), 
     --Appending `Pro` to each value                          
     tab_final as            (  Select col1,col2, case when col3 is not null 
                                                  then 'pro'||col3
                                                  else col3
                                                  end col3
                            from tab )
 --Displaying result as expected                           
SELECT
    col1,
    col2,
    LISTAGG(col3,',') WITHIN GROUP( ORDER BY col1,col2 ) col3
FROM
    tab_final
GROUP BY
    col1,
    col2

Demo:

--Preparing dataset  
 With                              
     customers(id,prdname,productid) as ( Select 1, 'cust1', '1,2' from dual
                               UNION ALL
                               Select  2,  'cust2','1,3' from dual
                               UNION ALL
                               Select 3,  'cust3','' from dual), 
    --Expanding each row seperated by comma                               
    tab(col1,col2,col3) as (                           
                             Select distinct c.id,c.prdname,regexp_substr(c.productid,'[^,]',1,level)
                              from customers c
                              connect by regexp_substr(c.productid,'[^,]',1,level) is not null
                              order by 1), 
     --Appending `Pro` to each value                          
     tab_final as            (  Select col1,col2, case when col3 is not null 
                                                  then 'pro'||col3
                                                  else col3
                                                  end col3
                            from tab )
 --Displaying result as expected                           
SELECT
    col1,
    col2,
    LISTAGG(col3,',') WITHIN GROUP( ORDER BY col1,col2 ) col3
FROM
    tab_final
GROUP BY
    col1,
    col2

PS: While using don't forget to put your actual table columns as in my example it may vary.

Upvotes: 2

Related Questions