Categories
sql

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

Categories
11gR2 sql

stragg in 11gR2

This will be a killer in the Oracle forums 😉
LISTAGG (measure_expr [, 'delimiter_expr'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

for instance
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) FROM emp;
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD