Fetch name based on comma-separated ids

Question

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?

Your database design is bound to cause a lot of problems. You shouldn't be storing `productid` as a comma separated value column. It will make your queries slower as you will need to use an additional logic to split the values and then join with `product` table. I would suggest you have a look at how to create _Many to Many relationship_ in sql — Sudipta Mondal, Aug 21 '18 at 05:32
It looks like a bad data model. First, you have to split the ids, there were already several questions and answers. https://stackoverflow.com/questions/41961621/splitting-a-comma-separated-string-in-oracle-not-working Afterwards you can join the product table and aggregate with the function `Listagg` — hotfix, Aug 21 '18 at 05:33
yes i know design is bad but i can't change it, i have to find the solution for it keeping design the same :( — Learner, Aug 21 '18 at 05:44
Possible duplicate of [How to use Oracle's LISTAGG function with a unique filter?](https://stackoverflow.com/questions/7355095/how-to-use-oracles-listagg-function-with-a-unique-filter) — kara, Aug 21 '18 at 06:04
it's not duplicate, here i have to fetch the name based on comma separated ids, i dont want to convert it in multiple rows. — Learner, Aug 21 '18 at 06:17

score 2 · Answer 1 · answered Aug 21 '18 at 06:33

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.

i need to make join with product table because i want to fetch name of the products instead of id, if for cust1 productids are 1,2 then in result it should display row like (1 cust1 pro1,pro2) — Learner, Aug 21 '18 at 06:52
@Learner and on which columns you want to join these 2 tables. Or do you say you want to compare `1` with `Pro1` and get `1` replaced with `Pro1` which is not not possible. — XING, Aug 21 '18 at 07:03
first i need to split 1,2 and remove comma, then i need to get pro1,pro2 from product table for both id then again i want to append pro1,pro2 with comma and display row like (1 cust1 pro1,pro2)..either by join or by subquery. — Learner, Aug 21 '18 at 07:06
@Learner you can refer this link. it seems this is also urs https://stackoverflow.com/questions/51928702/converting-comma-separated-value-to-multiple-rows/51928950?noredirect=1#comment90834690_51928950 — XING, Aug 21 '18 at 07:07
i have referred that link but that's slight different, i don't want to convert records in multiple rows, if productids are 1,2 then in same row i want to replace them with corresponding name of products, i.e. pro1,pro2. — Learner, Aug 21 '18 at 07:09
@Learner Try to incorporate which I showed there, The result will be what you need since it already have something with would convert the result as you want. Check for `Listagg`. — XING, Aug 21 '18 at 07:11
pro1,pro2,pro3 these records are just for example, in my case here i have 500+ rows in both tables... — Learner, Aug 21 '18 at 07:15
@Learner that's fine. My code has nothing to do with number of rows. — XING, Aug 21 '18 at 07:16
executed your query, but it is showing productids instead of names, i want names of products. — Learner, Aug 21 '18 at 07:20
@Learner Please close this question by accepting the answer if satisfied and ask another one showing what you did and what your expect as result. it would be diffuclt to say what you did and what your got until we see it, — XING, Aug 21 '18 at 07:22

Fetch name based on comma-separated ids

1 Answers1