CODEWITHSUNDEEP
sqloracle-databasestring-aggregation
rinson jimmy
rinson jimmy

Reputation: 37

How to get the values seperated by comma in a single column using SQL

I have the below table and expected result. Please let me know if it is possible to achieve the result. Please refer the picture attached.

enter image description here

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use listagg():

select e.id, e.name, e.sal,
       listagg(d.dept, ',') within group (order by d.dept_id) as depts,
       listagg(d.dept_id, ',') within group (order by d.dept_id) as dept_ids,
from employee e left join
     department d
     on e.name = d.name
group by e.id, e.name, e.sal;

Some comments on the data model.

  • Your department table should have a dept_id that is the primary key (no duplicates).
  • Your table that is called department should really be called employee_departments because it is a junction table, combining two different entities.
  • This table should be using emp_id as the link to employee, not name. That is, the foreign key relationship should be to the primary key of employee.

Upvotes: 2

Related Questions