Combine more than one Row in One Row
Working On Oracle Sample Database
Tables ( Departments and Employees)
1- Suppose we want to select employees Names in each department
Query :
| SELECT departments.department_id, departments.department_name, employees.first_name || ' ' || employees.last_name FROM employees JOIN departments ON departments.department_id = employees.department_id ORDER BY departments.department_id; |
Results :
10 Administration Jennifer Whalen
20 Marketing Michael Hartstein
20 Marketing Pat Fay
30 Purchasing Den Raphaely
30 Purchasing Karen Colmenares
30 Purchasing Alexander Khoo
30 Purchasing Shelli Baida
30 Purchasing Sigal Tobias
30 Purchasing Guy Himuro
40 Human Resources Susan Mavris
50 Shipping Donald OConnell
.................................................................
we want to know how we can make the results like :
10 Administration Jennifer Whalen
20 Marketing Michael Hartstein, Pat Fay
30 Purchasing Alexander Khoo, Den Raphaely, Guy Himuro, Karen Colmenares, Shelli Baida, Sigal Tobias
40 Human Resources Susan Mavris
Here is the way to do that without making any Functions or Procedures
| SELECT department_id, department_name, LTRIM(MAX(sys_connect_by_path(emp_name, ',')) keep(dense_rank LAST ORDER BY curr), ',') AS employees_names FROM ( SELECT employees.department_id, department_name, employees.first_name || ' ' || employees.last_name AS emp_name, row_number() over(PARTITION BY employees.department_id ORDER BY first_name) AS curr, row_number() over(PARTITION BY employees.department_id ORDER BY first_name) -1 AS prev FROM employees JOIN departments ON departments.department_id = employees.department_id ORDER BY employees.department_id ) GROUP BY department_id, department_name CONNECT BY prev = PRIOR curr AND department_id = PRIOR department_id START WITH curr = 1; |
I hope that this Query will help you
Thanks
Salaam
Comments