How to expand table column values ​​into one field in PL/SQL?

Question

Oracle

Good afternoon, tell me please, there is a table of the following kind:
ID ; LOGIN
1 ; IVANOV
2 ; IVANOV
3 ; IVANOV
4 ; PETROV
1 ; PETROV
8 ; TIHONOV It is
necessary to make sure that when querying the table, the information has the following form
LOGIN ; IDS
IVANOV ; 1|2|3
PETROV ; 4|1
TIHONOV ; 8
i.e. all login IDs are merged and through the separator | were in the same field.
It seems like LISTAGG can help in PL / SQL, but in my real case, the ID is much longer, and for certain logins it does not fit in 4096 bytes.
Maybe there is another solution?

Answer 1

Easy:

with function f (p_login varchar2) return varchar2
is
l_retval varchar2(32000);
begin
for x in (select id from t where login = p_login)
loop
 l_retval:= l_retval||'|'||x.id;
end loop;
return l_retval;
end;
select plogin, f(plogin) from (
select distinct login as plogin from t
)
/

The example above will only work with version 12s. But the essence does not change, arrange it in a function following the example of f.

Answer 2

As you pointed out listagg won't work because of the large length of the list of values. In essence, the logic behind this is this: there is no point in showing the user a giant list of values. Usually I did an incomplete output with <...> at the end, if all the values ​​​​did not fit in the list (listagg will not work here, you need a store). At the same time, you can fall into the list and get a complete list with pagination in the form of a table.
If we are talking about automatic processing by the application, then server-side aggregation is probably not a suitable solution. You can also look in the direction of XMLAGG or maybe there is already something for json.