Clash Royale CLAN TAG#URR8PPP

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

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

listagg()

4 Answers
4

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.

col1

col2

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.

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 -

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.

col1

col2

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.

CONNECT BY

REGEXP_SUBSTR

Rolling up at the end is done via LISTAGG.

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 |
+----+---------+-------------+---------------------+


listagg()

cross apply

greatest()

CROSS APPLY

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

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)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.