Home > 11gR2, sql > stragg in 11gR2

stragg in 11gR2

September 1st, 2009
Goto comments Leave a comment

This will be a killer in the Oracle forums ;)
listagg

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

Bookmark and Share

  1. Nicolas Gasparotto
    September 1st, 2009 at 17:20 | #1
    Reply | Quote

    Good catch !

  2. Waldar
    September 1st, 2009 at 20:12 | #2
    Reply | Quote

    So it won’t be wm_concat, but I’m fine with it as long as LISTAGG is documented and performs fast !

  3. DanyC
    September 1st, 2009 at 23:02 | #3
    Reply | Quote

    Coolio :)

  4. Centinul
    September 2nd, 2009 at 02:21 | #4
    Reply | Quote

    I was very happy when I saw this in the New Features Guide this morning!

    Don’t forget the NTH_VALUE function as well :)

  5. Surachart Opun
    September 2nd, 2009 at 05:03 | #5
    Reply | Quote

    Cool

  6. Scott
    September 4th, 2009 at 03:45 | #6
    Reply | Quote

    I was very happy to see the new analytical features, can’t wait to give them a whirl.

    Be interesting to compare the performance of this stragg feature against the others too.

  7. RC
    September 13th, 2009 at 07:47 | #7
    Reply | Quote

    The collect function has also 2 extra possibilities in 11gr1 and 11gr2. You can sort and you can do a distinct. You can use this to build some kind of variant on listagg.

    Somehow these 2 new possibilities aren’t mentioned in the new feautures docs.

  8. Laurent Schneider
    September 14th, 2009 at 11:04 | #8
    Reply | Quote

    select collect(ename order by ename) from emp

    also works in 10g, it was just not documented

  1. September 2nd, 2009 at 15:01 | #1
    11gR2 New Features – parte I « Oracle and other