Separate LISTAGG inside a subquery in the SELECT list

Question

Here is a minimal working example of what I am trying to do and what I am getting:

I have a request:

/* with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE (select 1 tran_party_id, 11 transaction_id, 101 team_id_redirect from dual union all select 2, 11, 101 from dual union all select 3, 11, 102 from dual union all select 4, 12, 103 from dual union all select 5, 12, 103 from dual union all select 6, 12, 104 from dual union all select 7, 13, 104 from dual union all select 8, 13, 105 from dual), tran as (select 11 transaction_id, 1001 account_id, 1034.93 amount from dual union all select 12, 1001, 2321.89 from dual union all select 13, 1002, 3201.47 from dual), account as (select 1001 account_id, 111 team_id from dual union all select 1002, 112 from dual), team as (select 101 team_id, 'UUU' as team_code from dual union all select 102, 'VV' from dual union all select 103, 'WWW' from dual union all select 104, 'XXXXX' from dual union all select 105, 'Z' from dual) -- */ -- The Actual Query select a.account_id, t.transaction_id, (select listagg (tm_redir.team_code, ', ') within group (order by tm_redir.team_code) from tran_party tp_redir inner join team tm_redir on tp_redir.team_id_redirect = tm_redir.team_id inner join tran t_redir on tp_redir.transaction_id = t_redir.transaction_id where t_redir.account_id = a.account_id and t_redir.transaction_id != t.transaction_id) as teams_redirected from tran t inner join account a on t.account_id = a.account_id; 

NOTE. tran_party.team_id_redirect is a foreign key that refers to the team.team_id command.

Current output:

 ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED ---------- -------------- ---------------- 1001 11 WWW, WWW, XXXXX 1001 12 UUU, UUU, VV 1002 13 

Expected Result:

I want the duplicate elements in the TEAMS_REDIRECTED column to TEAMS_REDIRECTED selected only once, for example:

 ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED ---------- -------------- ---------------- 1001 11 WWW, XXXXX 1001 12 UUU, VV 1002 13 

What I tried:

Instead of directly selecting from tran_party I wrote an inline view that selects various values ​​from tran_party as follows:

 select a.account_id, t.transaction_id, (select listagg (tm_redir.team_code, ', ') within group (order by tm_redir.team_code) from (select distinct transaction_id, team_id_redirect -- Note this inline view from tran_party) tp_redir inner join team tm_redir on tp_redir.team_id_redirect = tm_redir.team_id inner join tran t_redir on tp_redir.transaction_id = t_redir.transaction_id where t_redir.account_id = a.account_id and t_redir.transaction_id != t.transaction_id) as teams_redirected from tran t inner join account a on t.account_id = a.account_id; 

Although this gives me the expected result, when I use this solution in my actual code, it takes about 13 seconds to get one line. Thus, I cannot use what I have already tried.

Any help would be appreciated.

Answer 1

The following method eliminates the built-in view for getting duplicates; it uses REGEXP_REPLACE and RTRIM for the LISTAGG function to get a separate result specified in the aggregated list . Thus, it will not perform more than one scan.

Adding this part to your code,

 RTRIM(REGEXP_REPLACE(listagg (tm_redir.team_code, ',') WITHIN GROUP (ORDER BY tm_redir.team_code), '([^,]+)(,\1)+', '\1'), ',') 

Modified Request -

 SQL> with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE 2 (select 1 tran_party_id, 11 transaction_id, 101 team_id_redirect 3 from dual 4 union all 5 select 2, 11, 101 from dual 6 union all 7 select 3, 11, 102 from dual 8 union all 9 select 4, 12, 103 from dual 10 union all 11 select 5, 12, 103 from dual 12 union all 13 select 6, 12, 104 from dual 14 union all 15 select 7, 13, 104 from dual 16 union all 17 select 8, 13, 105 from dual), 18 tran as 19 (select 11 transaction_id, 1001 account_id, 1034.93 amount from dual 20 union all 21 select 12, 1001, 2321.89 from dual 22 union all 23 select 13, 1002, 3201.47 from dual), 24 account as 25 (select 1001 account_id, 111 team_id from dual 26 union all 27 select 1002, 112 from dual), 28 team as 29 (select 101 team_id, 'UUU' as team_code from dual 30 union all 31 select 102, 'VV' from dual 32 union all 33 select 103, 'WWW' from dual 34 union all 35 select 104, 'XXXXX' from dual 36 union all 37 select 105, 'Z' from dual) 38 -- The Actual Query 39 select a.account_id, 40 t.transaction_id, 41 (SELECT RTRIM( 42 REGEXP_REPLACE(listagg (tm_redir.team_code, ',') 43 WITHIN GROUP (ORDER BY tm_redir.team_code), 44 '([^,]+)(,\1)+', '\1'), 45 ',') 46 from tran_party tp_redir 47 inner join team tm_redir 48 on tp_redir.team_id_redirect = tm_redir.team_id 49 inner join tran t_redir 50 on tp_redir.transaction_id = t_redir.transaction_id 51 where t_redir.account_id = a.account_id 52 and t_redir.transaction_id != t.transaction_id) 53 AS teams_redirected 54 from tran t inner join account a on t.account_id = a.account_id 55 / ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED ---------- -------------- -------------------- 1001 11 WWW,XXXXX 1001 12 UUU,VV 1002 13 SQL>