one more stragg


select
to_char(
sum(
power(100,rownum-1)*
deptno
),
'FM99G99G99G99G99',
'NLS_NUMERIC_CHARACTERS=,;'
) deptlist
from dept

DEPTLIST
---------------
40;30;20;10


I also wrote about distinct listagg. The same applies for sum distinct.


select
to_char(
sum(power(1e3,d-1)*deptno),
'FM999G999G999',
'NLS_NUMERIC_CHARACTERS=,;'
) deptsum,
to_char(
sum(distinct power(1e2,d-1)*deptno),
'FM99G99G99',
'NLS_NUMERIC_CHARACTERS=,;'
) deptsumdist,
to_char(
sum(power(1e1,d-1)),
'FM9G9G9',
'NLS_NUMERIC_CHARACTERS=,;'
) deptcount,
to_char(
sum(power(1e4,c-1)*comm),
'FM9999G9999G9999G9999G9999',
'NLS_NUMERIC_CHARACTERS=,;'
) commlist
from (
select comm, deptno,
dense_rank() over (order by deptno) d,
dense_rank() over (order by comm) c
from emp);

DEPTSUM DSUMDIST COUNT COMMLIST
------------ -------- ----- -------------------
180;100;030 30;20;10 6;5;3 1400;0500;0300;0000