LISTAGG Function : New function in Oracle 11gR2

Prior to the release of Oracle 11gR2, if the list of employees needs to be displayed as 'Semi-colon' separated values along with the department id, the following SQL was used

SELECT t.department_id Dept , MAX(SYS_CONNECT_BY_PATH(first_name,' ;')) as Employees FROM (SELECT department_id , first_name , ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY department_id , first_name) rn FROM ( SELECT DISTINCT department_id , first_name FROM employees)) t START WITH t.rn = 1 CONNECT BY t.rn = PRIOR t.rn + 1 AND department_id = prior department_id GROUP BY department_id;

Output

Dept	Employees
10	; Jennifer
20	; Michael; Pat
30	; Alexander; Den; Guy; Karen; Shelli; Sigal
40	; Susan
50	; Adam; Alana; Alexis; Anthony; Britney; Curtis; Donald; Douglas; Girard; Hazel; Irene; James; Jason; Jean; Jennifer; John; Joshua; Julia; Kelly; Kevin; Ki; Laura; Martha; Matthew; Michael; Mozhe; Nandita; Payam; Peter; Randall; Renske; Samuel; Sarah; Shanta; Stephen; Steven; TJ; Timothy; Trenna; Vance; Winston
60	; Alexander; Bruce; David; Diana; Valli
70	; Hermann
80	; Alberto; Allan; Alyssa; Amit; Charles; Christopher; Clara; Danielle; David; Eleni; Elizabeth; Ellen; Gerald; Harrison; Jack; Janette; John; Jonathon; Karen; Lindsey; Lisa; Louise; Mattea; Nanette; Oliver; Patrick; Peter; Sarath; Sundar; Sundita; Tayler; William
90	; Lex; Neena; Steven
100	; Daniel; Ismael; John; Jose Manuel; Luis; Nancy
110	; Shelley; William
	; Kimberely

Now, Oracle 11gR2, Oracle provided a columnar function called LISTAGG

Syntax:

LISTAGG (measure_column_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

This LISTAGG function flattens the results into a single record by combining the values. This function can be used in one-to-many constructions.

One of the fun little features - to switch gears here - that is added to SQL is in analytics. We now have something called LISTAGG. LISTAGG allows you to flatten a result set into a single record. This is often

used in 1:many constructions such as hierarchical data and bill of materials.

The above output can be achieved by

SELECT department_id , LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees" FROM employees GROUP BY department_id;

For more info, click here