Oracle 11g how I can group values from two different columns

Question

I have an oracle VIEW that contains some values inside two columns I want to group those values into a new column adding concatination:

myView : 

---------------------------------
ID  | col 1       | col 2        |
---------------------------------
1   | 1,2,3,4     |V1,V2,V3,V4
2   | 4,5,6,7     |V5,V6,V7,V8

I want to create new view adding a new column col 3 like this :

 ------------------------------------------------------
    ID  | col 1       | col 2      |col 3
 ------------------------------------------------------
    1   | 1,2,3,4     |V1,V2,V3,V4 |1,V1 2,V2 3,V3 4,V4
    2   | 5,6,7,8     |V5,V6,V7,V8 |5,V5 6,V6 7,V7 8,V8

Thanks in advance for any help

Answer 1

Another option would be using custom pl/sql function.

CREATE OR REPLACE FUNCTION "STR_TO_TABLE" 
  (in_strt   in varchar2,
   in_delim in varchar2 default ',')
  return      str_table
as
  l_str  clob default in_strt || in_delim;
  l_n    number;
  l_data str_table := str_table();
begin
  loop
      l_n := instr( l_str, in_delim );
      exit when (nvl(l_n,0) = 0);
      l_data.extend;
      l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
      l_str := substr( l_str, l_n+length(in_delim) );
  end loop;
  return l_data;
end;

/

create or replace function custom_concat(col1 varchar2,col2 varchar2) 
return varchar2
is
conct_val varchar2(4000);
begin
  select listagg(final, ' ') within group (order by 1) into conct_val from (
  select r1,M.column_value mcv,s.column_value scv,s.s1,M.column_value||','||s.column_value final from (select rownum r1,column_value from table(STR_TO_TABLE(col1))) M join (select rownum s1,column_value from table(STR_TO_TABLE(col2))s1) s on (r1=s1) );
return conct_val;
end;
/

Then use it like this -

select col1,col2,custom_concat(col1,col2) from temp_123;

Result -

Answer 2

As sort of requested by Matthew, an 11gR2-compatible version of exploding the comma-separated lists, using the same extended sample data:

with input_data ( id, col1, col2 ) as (
SELECT 1   , '1,2,3,4',     'V1,V2,V3,V4' from dual union all
SELECT 2   , '4,5,6,7',     'V5,V6,V7,V8' from dual union all
SELECT 3   , 'A',           'VA,VB,VC,VD' from dual union all
SELECT 4   , 'E,F,G',       'VE' from dual union all
SELECT 5   , 'H,I',         '' from dual union all
SELECT 6   , '',            'J,K' from dual
)
, cte (id, col1, col2, pos, combined_value) as (
  select id, col1, col2, level,
    regexp_substr(col1, '(.*?)(,|$)', 1, level, null, 1)
      ||','|| regexp_substr(col2, '(.*?)(,|$)', 1, level, null, 1)
  from input_data
  connect by id = prior id
  and prior dbms_random.value is not null
  and level <= greatest(nvl(regexp_count(col1, ','), 0),
    nvl(regexp_count(col2, ','), 0)) + 1
)
select id,
       col1,
       col2,
       listagg(combined_value, ' ') within group (order by pos) as col3
from cte
group by id, col1, col2;

        ID COL1    COL2        COL3                          
---------- ------- ----------- ------------------------------
         1 1,2,3,4 V1,V2,V3,V4 1,V1 2,V2 3,V3 4,V4           
         2 4,5,6,7 V5,V6,V7,V8 4,V5 5,V6 6,V7 7,V8           
         3 A       VA,VB,VC,VD A,VA ,VB ,VC ,VD              
         4 E,F,G   VE          E,VE F, G,                    
         5 H,I                 H, I,                         
         6         J,K         ,J ,K                         

The additional CTE convert the appropriate column values into a list of separate numbers, concatenated together for each ID and position with in the list. And as in Matthew's answer the all the concatenated values for each ID are aggregated into a space-separated string.

---

However, it would still be simpler and probably more efficient to go back to the source of the current view - assuming that is itself creating the col1 and col2 values through string aggregation - and base your new query/view on that original query.

Building views on top of other views can cause performance issues as the optimiser can't always pass predicates up to the right point. But creating aggregated lists of values, splitting them up, then re-aggregating them is just doing more work than you need.

Answer 3

You need to do in PLSQL (or a bigger sql query) for each row:

1. Split col1 by delimiter and save to an array
2. Split col2 by a delimiter and save to an array
3. for i=0 parse the array and concat to a string array1[i] || ',' ||array2[i] || ' '
4. Insert the result string to the col3

This is how the splitting method done:

SELECT num_value
      FROM (SELECT   TRIM (REGEXP_SUBSTR (num_csv, '[^,]+', 1, LEVEL)) num_value
              FROM   ( SELECT   col1 num_csv FROM table_view)
              CONNECT BY   LEVEL <= regexp_count (num_csv, ',', 1) + 1)

Answer 4

You need to split the comma-separated col1 and col2 values into rows, then concatenated each row, and then roll up the concatenations back into one by comma-separated string.

Splitting is done using the well-known trick of using CONNECT BY to generate one "dummy" row per entry in the list and then using REGEXP_SUBSTR to pick out each comma-separated value.

Rolling up at the end is done via LISTAGG.

Here it is all together (with extra test data to account for mismatches in the number of elements in each column):

with input_data ( id, col1, col2 ) as (
SELECT 1   , '1,2,3,4',     'V1,V2,V3,V4' from dual union all
SELECT 2   , '4,5,6,7',     'V5,V6,V7,V8' from dual union all
SELECT 3   , 'A',           'VA,VB,VC,VD' from dual union all
SELECT 4   , 'E,F,G',       'VE' from dual union all
SELECT 5   , 'H,I',         '' from dual union all
SELECT 6   , '',            'J,K' from dual
)
select i.id,
       i.col1,
       i.col2,
       listagg(trim(regexp_substr(i.col1, '[^,]+', 1, p.pos)) ||
          ',' || trim(regexp_substr(i.col2, '[^,]+', 1, p.pos)),',') 
          within group ( order by p.pos ) col3
from input_data i
cross apply ( select rownum pos 
              FROM dual 
              connect by level <= 
                        greatest(nvl(regexp_count(i.col1,','),0),
                                 nvl(regexp_count(i.col2,','),0)) +1 ) p
group by i.id, i.col1, i.col2;

Results:

+----+---------+-------------+---------------------+
| ID |  COL1   |    COL2     |        COL3         |
+----+---------+-------------+---------------------+
|  1 | 1,2,3,4 | V1,V2,V3,V4 | 1,V1,2,V2,3,V3,4,V4 |
|  2 | 4,5,6,7 | V5,V6,V7,V8 | 4,V5,5,V6,6,V7,7,V8 |
|  3 | A       | VA,VB,VC,VD | A,VA,,VB,,VC,,VD    |
|  4 | E,F,G   | VE          | E,VE,F,,G,          |
|  5 | H,I     |             | H,,I,               |
|  6 |         | J,K         | ,J,,K               |
+----+---------+-------------+---------------------+