Oracle 11g new collection function listparts implements column forwarding

Oracle 11g new collection function listparts implements column forwarding

First, the syntax is as follows:
Listparts (column | expression,
Delimiter) within group (order by column | expression) is a clustering function. Use an example to describe its usage: SELECT department_id, listparts (last_name, ';') within group (order by hire_date, last_name) "Emp_list" FROM employee
Group by department_id; 10 Eckhardt; Newton; Friedli; James; Michaels; Dovichi
20 peterson; leblanc
30 Jeffrey; Wong
Newton can see that a simple line of code implements the column-to-row function. Before that, decode () should be used to implement various possibilities. If it is not possible to do so, you need to write a large number of functions (see asktom.oracle.com for details ).
In this example, last_name is clustered according to department_id, and all last_name under the same department_id are grouped as a group.

Listparts as an analysis function

SELECT department_id "Dept", hire_date "Date", last_name "Name ",
Listparts (last_name, ';') within group (order by hire_date, last_name)
OVER (partition by department_id) as "Emp_list"
FROM employee
Order by "Dept", "Date", "Name ";
This SQL query shows each person in each department, his employment date, and all colleagues in the same department. Sort by department, employment date, and employee name.

102004/07/07 EckhardtEckhardt; Newton; Friedli; James; Michaels; Dovichi
102006/09/24 NewtonEckhardt; Newton; Friedli; James; Michaels; Dovichi
102007/05/16 FriedliEckhardt; Newton; Friedli; James; Michaels; Dovichi
102007/05/16 JamesEckhardt; Newton; Friedli; James; Michaels; Dovichi
102007/05/16 MichaelsEckhardt; Newton; Friedli; James; Michaels; Dovichi
102011/07/07 DovichiEckhardt; Newton; Friedli; James; Michaels; Dovichi
202008/11/03 petersonpeterson; leblanc
202009/03/06 leblancpeterson; leblanc
302010/02/27 JeffreyJeffrey; Wong
302010/02/27 WongJeffrey; Wong
2005/09/14 NewtonNewton

Recommended reading:

Replace () for Oracle Functions ()

Oracle Functions

Oracle function for upgrading a 15-digit ID card to a 18-digit ID card