Returning multiple identical rows in LISTAGG query

I’m using Oracle, and want to connect two tables into one select statement (for a report), and we’re required to concatenate some of the data from one table into a single row – for which I’m expecting to use LISTAGG.

Simplified version of the problem:

ACCOUNTS table:

InitialDate	AccountNumber	Balance
01/01/1980	11111	20
02/01/1980	22222	30
03/01/1980	33333	40
04/01/1980	44444	50

ACCOUNT_TO_CUST table:

AccountNumber	CustNo
11111	50
22222	51
22222	52
33333	53
44444	51
44444	55
44444	57

And what I’m trying to get is something like:

Account Number	Customers	Initial Date	Balance
11111	50	01/01/1980	20
22222	51,52	02/01/1980	30
33333	53	03/01/1980	40
44444	51,55,57	04/01/1980	50

However, what I’m actually seeing are duplicate rows, which breaks the reporting tools:

Account Number	Customers	Initial Date	Balance
11111	50	01/01/1980	20
22222	51,52	02/01/1980	30
22222	51,52	02/01/1980	30

The query I have currently is

SELECT a.AccountNumber,
LISTAGG(ac.Customers, ',') WITHIN GROUP (ORDER BY a.AccountNumber) 
a.InitialDate, 
a.balance
FROM accounts a, account_to_cust ac
WHERE accounts.AccountNumber = account_to_cust.AccountNumber
Group By a.AccountNumber, a.InitialDate, a.balance

I’ve tried putting a distinct into the initial select, and get an error that the concatenation is too long, and I’ve tried adding the distinct in the LISTAGG itself, which doesn’t seem to work either. How do I eliminate these duplicates?

Answer

This looks OK to me (sample data in lines #1 – 16; query begins at line #17):

SQL> with
  2  accounts (initialdate, accountnumber, balance) as
  3    (select date '1980-01-01', 11111, 20 from dual union all
  4     select date '1980-01-02', 22222, 30 from dual union all
  5     select date '1980-01-03', 33333, 40 from dual union all
  6     select date '1980-01-04', 44444, 50 from dual
  7    ),
  8  account_to_cust (accountnumber, custno) as
  9    (select 11111,   50 from dual union all
 10     select 22222,   51 from dual union all
 11     select 22222,   52 from dual union all
 12     select 33333,   53 from dual union all
 13     select 44444,   51 from dual union all
 14     select 44444,   55 from dual union all
 15     select 44444,   57 from dual
 16    )
 17  select
 18    a.accountnumber,
 19    a.initialdate,
 20    a.balance,
 21    listagg(b.custno, ', ') within group (order by b.custno) customers
 22  from accounts a join account_to_cust b on b.accountnumber = a.accountnumber
 23  group by a.accountnumber,
 24           a.initialdate,
 25           a.balance
 26  order by a.accountnumber;

ACCOUNTNUMBER INITIALDATE        BALANCE CUSTOMERS
------------- --------------- ---------- ---------------
        11111 01/01/1980              20 50
        22222 02/01/1980              30 51, 52
        33333 03/01/1980              40 53
        44444 04/01/1980              50 51, 55, 57

SQL>

As you can see, no duplicates.