kumar kumar - 1 month ago 15
SQL Question

How to concatenate strings from two columns based on userid column and the result should contain distinct values?

TABLE OUTPUT TABLE

U_ID|PRODUCT|BRANCH U_ID RESULT
1 AL 8 1 8~AL-BL+1~HG-IK
1 BL 8
1 HJ 1
1 IK 1
2 IK 6
2 Po 8
3 UY 6


As shown in the snippet strings from two columns should be concatenated based on u_id column and in this requirement the final concatenated string should be distinct..

It is a Oracle database

I tried using LISTAGG function but not getting result for two columns.. any idea will help me a lot.. thanks

Answer Source

This could be done, by eliminating duplicates from the listagg strings:

       with data as (
select 1 as id,    'AL' as PRODUCT,   8 as BRANCH   from dual union all               
select 1 as id,    'BL' ,   1  from dual union all               
select 1 as id,    'HJ ',   1  from dual union all               
select 2 as id,    'IK' ,   5  from dual union all                   
select 2 as id,    'IK' ,   6  from dual union all                   
select 2 as id,    'Po' ,   8  from dual union all                   
select 3 as id,    'UY' ,   6  from dual
)
select id, PRODUCT||'~'||BRANCH as RESULT from( 
select id,
REGEXP_REPLACE(
listagg(PRODUCT,'-') within group (order by id), 
 '([^-]*)(-\1)+($|-)','\1\3') PRODUCT, 
REGEXP_REPLACE(
           (listagg( BRANCH,'-') within group (order by id)), 
           '([^-]*)(-\1)+($|-)','\1\3') BRANCH
 from data 
 group by id
 )

Credits for duplicate eliminatuion must go to jack douglass

Results:

ID  Result
1   AL-BL-HJ~1-8
2   IK-Po~5-6-8
3   UY~6