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


 

Comments