CODEWITHSUNDEEP
sqloracle-database
navy1978
navy1978

Reputation: 1439

Create a view of a table with a column that has multiple values

I have a table (Table1) like the following:

Col1 Col2
First Code1,Code2,Code3
Second Code2

So Col2 can contain multiple values comma separated, I have another table (Table2) that contains this:

ColA ColB
Code1 Value1
Code2 Vaue2
Code3 Vaue3

I need to create a view that joins the two tables (Table1 and Table2) and returns something like this:

Col1 Col2
First Value1,Value2,Value3
Second Value2

Is that possible? (I'm on Oracle DB if that helps.)

Upvotes: 0

Views: 605

Answers (2)

Paul W
Paul W

Reputation: 11468

It's a violation of first normal form to have a list in a column value like that. It causes a lot of difficulties in a relational database, like the one you are encountering now.

However, you can get what you want by using the LIKE operator to find colA values that are substrings of the Col2 column. Add delimiters before and after to catch the first and last ones. Then aggregate back up to a single list using LISTAGG.

SELECT table1.col1,
       LISTAGG(table2.colB,',') WITHIN GROUP (ORDER BY table2.colB) value_list
  FROM table1,
       table2
 WHERE ','||table1.col2||',' LIKE '%,'||table2.colA||',%'
 GROUP BY table1.col1

This will not perform well on large volumes, because without an equijoin it's going to use nested loops, and you can't use an index on a LIKE predicate with % at the beginning. The combination of nested loops + FTS is not pleasant with large volumes of data. Therefore, if this is your situation, you will need to fix the 1NF problem by transforming table1 into normal relational format, and then join it to table2 with an equijoin, which will enable it to use a hash join instead. So:

SELECT table1.col1,
       LISTAGG(table2.colB,',') WITHIN GROUP (ORDER BY table2.colB) value_list
  FROM (SELECT t.col1,
               SUBSTR(t.col2,INSTR(t.col2,',',1,seq)+1,INSTR(t.col2,',',1,seq+1)-(INSTR(t.col2,',',1,seq)+1)) col2_piece
          FROM (SELECT col1,
                       ','||col2||',' col2
                  FROM table1) t,
               (SELECT ROWNUM seq FROM dual CONNECT BY LEVEL < 10) x) table1,
       table2
 WHERE table1.col2_piece IS NOT NULL
   AND table1.col2_piece = table2.colA
 GROUP BY table1.col1

Upvotes: 1

MT0
MT0

Reputation: 168051

If you want the values in the same order in the list as the terms then you can use:

SELECT t1.col1,
       LISTAGG(t2.colb, ',') WITHIN GROUP (
         ORDER BY INSTR(','||t1.col2||',', ','||t2.colA||',')
       ) AS value2
FROM   table1 t1
       INNER JOIN table2 t2
       ON INSTR(','||t1.col2||',', ','||t2.colA||',') > 0
GROUP BY 
       t1.col1

Which, for the sample data:

CREATE TABLE Table1 (Col1, Col2) AS
SELECT 'First',  'Code1,Code2,Code3' FROM DUAL UNION ALL
SELECT 'Second', 'Code2' FROM DUAL;

CREATE TABLE Table2 (ColA, ColB) AS
SELECT 'Code1', 'XXXX' FROM DUAL UNION ALL
SELECT 'Code2', 'ZZZZ' FROM DUAL UNION ALL
SELECT 'Code3', 'YYYY' FROM DUAL;

Outputs:

COL1 VALUE2
First XXXX,ZZZZ,YYYY
Second ZZZZ

fiddle

Upvotes: 0

Related Questions