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

Popular posts from this blog

SQL Interview Questions

Manage Your Database Connection

Number of Days in Current Month