CODEWITHSUNDEEP
sqloracle-database
Nursultan Bekenov
Nursultan Bekenov

Reputation: 13

Oracle Create a view replacing ids with names in column (not 1nf)

We have for example this table:

pl_num camp_type  products
1       T         1,2,3
2       B         1,3,4

Yeah, I know it's not in 1NF but we need to work with it because of application loads data in such way.

And we have table DICT_PRODUCT, for example (in reality, there are more than 500 product):

id product_name
1   a
2   b
3   c
4   d

So, what we need is to create view where product_id was replaced by its name in dictionary
---V_TAB1 ---

pl_num camp_type  products
1       T         1,b,c
2       B         a,c,d

Upvotes: 0

Views: 59

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Try this. It will work if products column in TAB1 contain numbers and not any other characters.

WITH prod
     AS (SELECT pl_num, camp_type, TO_NUMBER (TRIM (COLUMN_VALUE)) product
           FROM Tab1 t, XMLTABLE (t.products))
  SELECT prod.pl_num,
         prod.camp_type,
         LISTAGG (d.product_name, ',') WITHIN GROUP (ORDER BY id) products
    FROM prod JOIN dict_product d ON prod.product = d.id
GROUP BY prod.pl_num, prod.camp_type;

DEMO

Upvotes: 2

Meow Meow
Meow Meow

Reputation: 666

Try this one:

   select distinct *
    from (
    select t.u_name, u_id, regexp_substr(t.prod,'[^,]+', 1, level) id
    from (select prod,u_id, u_name from  cmdm.t_prod) t
    connect by regexp_substr(prod,'[^,]+',1,level) is not null) ut
    inner join cmdm.t_dct dt
    on ut.id=dt.id

Upvotes: 0

Related Questions