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
Post a Comment