Sunday, December 11, 2011

11g New feature: listagg




SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select department_id dept,first_name
2 from employees
3 where department_id<50
4 order by department_id,first_name;

DEPT FIRST_NAME
---------- --------------------------------------------------
10 Jennifer
20 Michael
20 Pat
30 Alexander
30 Den
30 Guy
30 Karen
30 Shelli
30 Sigal
40 Susan

10 rows selected.


SQL> select department_id dept,listagg(first_name,';') within group (order by first_name) first_name
2 from employees
3 where department_id<50
4 group by department_id
5 order by department_id;

DEPT FIRST_NAME
---------- --------------------------------------------------
10 Jennifer
20 Michael;Pat
30 Alexander;Den;Guy;Karen;Shelli;Sigal
40 Susan