Tag Archives: stragg

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

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