1
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

Cœur
  • 37,241
  • 25
  • 195
  • 267
kumar
  • 15
  • 5

2 Answers2

1

This is tricky. Oracle doesn't support distinct with listagg(), so you need to use subqueries:

select pu.u_id,
       (products || '~' || branches) as result
from (select u_id, listagg(product, '-') within group (order by product) as products
      from (select distinct u_id, product from t) pu
      group by u_id
     ) pu join
     (select u_id, listagg(branch, '-') within group (order by branch) as branches
      from (select distinct u_id, branch from t) bu
      group by u_id
     ) bu
     on pu.u_id = bu.u_id
group by pu.u_id;

EDIT:

There is another way to do this without so many subqueries:

select u_id,
       (listagg(case when seqnum_p = 1 then product end, '-') within group (order by product) ||
        '~' ||
        listagg(case when seqnum_b = 1 then branch end, '-') within group (order by branch)
       ) as result
from (select t.*,
             row_number() over (partition by u_id, product order by product) as seqnum_p,
             row_number() over (partition by u_id, branch order by branch) as seqnum_b
      from t
     ) t
group by u_id;

Here is an example in rextester.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your time...ORA-02000: missing WITHIN keyword 02000. 00000 - "missing %s keyword" Error at Line: 3 Column: 50 .......... getting this error .. please share your views – kumar Jun 22 '17 at 11:08
  • @kumar . . . Yes, of course. That syntax would need to be correct! – Gordon Linoff Jun 22 '17 at 11:16
  • http://www.oracle-developer.net/display.php?id=515 ...... added WITHIN keyword but now getting ORA-00979 .. – kumar Jun 22 '17 at 11:18
  • For the EDIT query : ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: Error at Line: 3 Column: 9 can you able to figure out!!!! – kumar Jun 22 '17 at 11:35
  • working great but the issue is that the row count is large so how to make the result in a new table .. awaiting your reply!! – kumar Jun 22 '17 at 12:11
  • @kumar . . . You just put `create table as ` before the query. – Gordon Linoff Jun 22 '17 at 12:17
0

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
PKey
  • 3,715
  • 1
  • 14
  • 39
  • Thanks.. but my table contains 10k records .. so how to edit this part: '([^-]*)(-\1)+($|-)','\1\3') PRODUCT ... awaiting your reply!! – kumar Jun 22 '17 at 11:24
  • @kumar Did not understand your question. What exactly do you want to edit in the above part ? If you mean that your result string is too long .. then you have a problem even without `regex_replace` part... – PKey Jun 22 '17 at 11:31
  • I am trying to say that I have 10k rows in by table.. so how to make the query work for that!!? ... issue is that in ID column its multiple digit id's present.. – kumar Jun 22 '17 at 11:41
  • @kumar So, what makes you think that this solution would not work for that 10k rows? Have you tried? Explain ... – PKey Jun 22 '17 at 11:43
  • thanks a lot .. you saved my time :) ..... can you share a idea to store the result in a new table ... awaiting your reply.. – kumar Jun 22 '17 at 11:51
  • Reason is that I can able to get the result but cant able to extract in excel nor create into new table... because of the count.. – kumar Jun 22 '17 at 11:53
  • @kumar How about `create table myresults as select ....` where `select ...` the select of the solution ... – PKey Jun 22 '17 at 11:55
  • @kumar [result-of-string-concatenation-is-too-long](https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long) – PKey Jun 22 '17 at 17:02