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

You shouldn't be storing comma separated values in the first place — , Oct 20 '15 at 06:41
Ideally, yes. But there's are use cases where normalizing all the data is expensive. And just because storing comma separate values in a column isn't ideal, doesn't mean the question is bad and needs to be downvoted :) — icedek, Oct 20 '15 at 06:44
@jarlh It is not a combination that I'm looking for. I have the flexibility to query either databases which have the same table. — icedek, Oct 20 '15 at 06:49
To me it seems a simple use case of **SUBSTR**, **INSTR** and **concatenation**. — Lalit Kumar B, Oct 20 '15 at 07:25

score 1 · Answer 1 · answered Oct 20 '15 at 07:20

You can do it like this using INSTR and SUBSTR:

   select 
   substr(column1,1,instr(column1,',',1)-1) || '|' ||
   substr(column2,1,instr(column2,',',1)-1) || '|' ||
   substr(column3,1,instr(column3,',',1)-1) || '|' ||
   ',' ||
   substr(column1 ,instr(column1 ,',',1,1)+1,instr(column1 ,',',1,2) - instr(column1 ,',',1)-1) || '|' || 
   substr(column2 ,instr(column2 ,',',1,1)+1,instr(column2 ,',',1,2) - instr(column2 ,',',1)-1) || '|' || 
   substr(column3 ,instr(column3 ,',',1,1)+1,instr(column3 ,',',1,2) - instr(column3 ,',',1)-1) || '|' || 
   ',' ||
   substr(column1 ,instr(column1 ,',',1,2)+1) || '|' || 
   substr(column2 ,instr(column2 ,',',1,2)+1) || '|' || 
   substr(column3 ,instr(column3 ,',',1,2)+1) 
   from yourtable

Smart003 · Answer 2 · 2015-10-20T09:04:51.807

i tried some thing. just look into link first i created a table called t_ask_test and inserted the data based on the above question. Achieved the result by using the string functions

sample table

 create table t_ask_test(column1 varchar(10), column2 varchar(10),column3 varchar(10));

inserted a row

insert into T_ASK_TEST values ('A,B,C','1,2,3','x,y,z');

the following query will be in dynamic way

select substr(column1,1,instr(column1,',',1,1)-1)||'|'||substr(column2,1,instr(column1,',',1,1)-1)||'|'||substr(column3,1,instr(column1,',',1,1)-1) ||','||
 substr(column1,instr(column1,',',1,1)+1,instr(column1,',',1,2)-instr(column1,',',1,1)-1)||'|'||substr(column2,instr(column2,',',1,1)+1,instr(column2,',',1,2)-instr(column2,',',1,1)-1)||'|'||substr(column3,instr(column3,',',1,1)+1,instr(column3,',',1,2)-instr(column3,',',1,1)-1) ||','||
 substr(column1,instr(column1,',',1,2)+1,length(column1)-instr(column1,',',1,2))||'|'||substr(column2,instr(column2,',',1,2)+1,length(column2)-instr(column2,',',1,2))||'|'||substr(column3,instr(column3,',',1,2)+1,length(column3)-instr(column3,',',1,2)) as test from t_ask_test;

output will be as follows

     TEST
---------------
A|1|x,B|2|y,C|3|z

MT0 · Answer 3 · 2015-10-20T09:51:41.907

If you have a dynamic number of entries for each row then:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( Column1, Column2, Column3 ) AS
          SELECT 'A,B,C', '1,2,3', 'x,y,z' FROM DUAL
UNION ALL SELECT 'D,E', '4,5', 'v,w' FROM DUAL;

Query 1:

WITH ids AS (
  SELECT t.*, ROWNUM AS id
  FROM   TEST t
)
SELECT LISTAGG(
                   REGEXP_SUBSTR( i.Column1, '[^,]+', 1, n.COLUMN_VALUE )
         || '|' || REGEXP_SUBSTR( i.Column2, '[^,]+', 1, n.COLUMN_VALUE )
         || '|' || REGEXP_SUBSTR( i.Column3, '[^,]+', 1, n.COLUMN_VALUE )
       , ','
       ) WITHIN GROUP ( ORDER BY n.COLUMN_VALUE ) AS value
FROM   ids i,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= GREATEST(
               REGEXP_COUNT( i.COLUMN1, '[^,]+' ),
               REGEXP_COUNT( i.COLUMN2, '[^,]+' ),
               REGEXP_COUNT( i.COLUMN3, '[^,]+' )
             )
           )
           AS SYS.ODCINUMBERLIST
         )
       ) n
GROUP BY i.ID

Results:

|             VALUE |
|-------------------|
| A|1|x,B|2|y,C|3|z |
|       D|4|v,E|5|w |

Thanks for this answer. However this wont work in DB2 – icedek Oct 20 '15 at 14:57 — icedek, Oct 20 '15 at 14:57

Lalit Kumar B · Accepted Answer · 2015-10-20T07:26:56.207

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.

score -1 · Answer 5 · answered Oct 20 '15 at 06:46

-1

Its very simple in oracle. just use the concatenation operatort ||. In the below solution, I have used underscore as the delimiter

select Column1 ||'_'||Column2||'_'||Column3 from table_name;

answered Oct 20 '15 at 06:46

Raj

35
1
9

Not really. The corresponding values need to be concatenated. With your above statement, the result would be "A,B,C_1,2,3_x,y,z" – icedek Oct 20 '15 at 06:47
What do you mean by corresponding values need to be concatenated. If the table is like A 1 x B 2 y C 3 Z my query would make it A_1_x B_2_y C_3_Z – Raj Oct 20 '15 at 06:48
Look at the "Result" in the question. – icedek Oct 20 '15 at 06:50
If the table is like A 1 x, B 2 y, C 3 Z, my query would make it A_1_x, B_2_y, C_3_Z, – Raj Oct 20 '15 at 06:54
The contents of the column are a comma separated list. Imagine there is only one row in each column and the value is 'A,B,C' for Column 1 – icedek Oct 20 '15 at 06:56
In that case you have to use regexp_sbstr ...look at this http://stackoverflow.com/questions/31654411/split-string-by-space-and-character-as-delimiter-in-oracle-with-regexp-substr – Raj Oct 20 '15 at 07:15

SQL Concatenate strings across multiple columns with corresponding values

5 Answers5