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 (… Continue reading one more stragg

Categorized as sql Tagged

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