How to achieve string concatentation of entries in column having same id in Oracle Analytics Cloud Professional Edition?

Question

I have a dataset in which one column is Branch-ID and other one is Branch Manager and it looks as follows in the given url. dataset

I want to combine the branch managers into one single column based on the branch-id. For example if Bob and Sandra are two different branch-managers but have the same branch id which is branch-id=1, then we should concatenate them together as Bob-Sandra and place them in a separately created column.

I have attached the expected output for the above dataset. expected_output_dataset

I am currently using Oracle Analytics Cloud Professional Version.

Answer 1

I don't know Oracle Analytics, but - if it has anything to do with an Oracle database and its capabilities, then listagg helps.

Sample data in lines #1 - 10; query you might be interested in begins at line #11.

SQL> with test (account_id, branch_id, branch_manager) as
  2    (select 1, 123, 'Sandra'  from dual union all
  3     select 3, 124, 'Martha'  from dual union all
  4     select 4, 125, 'John'    from dual union all
  5     select 6, 126, 'Andrew'  from dual union all
  6     select 7, 126, 'Mathew'  from dual union all
  7     select 2, 123, 'Michael' from dual union all
  8     select 5, 125, 'David'   from dual union all
  9     select 8, 126, 'Mark'    from dual
 10    )
 11  select a.account_id, a.branch_id, a.branch_manager,
 12    b.concatenated_column
 13  from test a join (select branch_id,
 14                      listagg(branch_manager, '-') within group (order by null) concatenated_column
 15                    from test
 16                    group by branch_id
 17                   ) b on b.branch_id = a.branch_id;

ACCOUNT_ID  BRANCH_ID BRANCH_ CONCATENATED_COLUMN
---------- ---------- ------- -------------------------
         1        123 Sandra  Michael-Sandra
         3        124 Martha  Martha
         4        125 John    David-John
         6        126 Andrew  Andrew-Mark-Mathew
         7        126 Mathew  Andrew-Mark-Mathew
         2        123 Michael Michael-Sandra
         5        125 David   David-John
         8        126 Mark    Andrew-Mark-Mathew

8 rows selected.

SQL>