SQL Concatenate strings across multiple columns with corresponding values

Question

I'm looking for a way to achieve this in a SELECT statement.

FROM

Column1    Column2    Column3
A,B,C      1,2,3      x,y,z

TO

Result
A|1|x,B|2|y,C|3|z

The delimiters don't matter. I'm just trying to to get all the data in one single column. Ideally I am looking to do this in DB2. But I'd like to know if there's an easier way to get this done in Oracle. Thanks

Answer 1

You need to use:

• SUBSTR
• INSTR
• || concatenation operator

It would be easy if you break your output, and then understand how it works.

SQL> WITH t AS
  2    ( SELECT 'A,B,C' Column1, '1,2,3' Column2, 'x,y,z' Column3 FROM dual
  3    )
  4  SELECT SUBSTR(column1, 1, instr(column1, ',', 1) -1)
  5    ||'|'
  6    || SUBSTR(column2, 1, instr(column2, ',', 1) -1)
  7    ||'|'
  8    || SUBSTR(column3, 1, instr(column1, ',', 1) -1)
  9    ||','
 10    || SUBSTR(column1, instr(column1, ',', 1, 2) +1 - instr(column1, ',', 1),
 11                       instr(column1, ',', 1) -1)
 12    ||'|'
 13    || SUBSTR(column2, instr(column2, ',', 1, 2) +1 - instr(column2, ',', 1),
 14                       instr(column2, ',', 1) -1)
 15    ||'|'
 16    || SUBSTR(column3, instr(column3, ',', 1, 2) +1 - instr(column3, ',', 1),
 17                      instr(column3, ',', 1) -1)
 18    ||','
 19    || SUBSTR(column1, instr(column1, ',', 1, 3) +1 - instr(column1, ',', 1),
 20                       instr(column1, ',', 2) -1)
 21    as "new_column"
 22  FROM t;

new_column
-------------
A|1|x,B|2|y,C

---

On a side note, you should avoid storing delimited values in a single column. Consider normalizing the data.

From Oracle 11g and above, you could create a VIRTUAL COLUMN using the above expression and use it instead of executing the SQL frequently.