Replacing comma separated integers in Oracle

Question

I have the following tables:

Employee:

Id	NAME	Qualification	Experience(yrs)
1	Aaron	Eng	2
2	Jacob	CS	3
3	Jehan	Art	1
4	Jenny	Math	7

Task:

Task_Name	Emp_id	duration(mins)	Wage
clean	1,3	200	50
wash	4	300	200
decor	1,2,4	250	300
errands	2,4	150	150

I would like to write an oracle query which returns the following output:

Task_responsbility:

Task_Name	Employee	duration(mins)	Wage
clean	Aaron,Jehan	200	50
wash	Jenny	300	200
decor	Aaron,Jacob,Jenny	250	300
errands	Jacob,Jenny	150	150

I need the Emp_ids to be replaced by employee_names

Can someone please let me know how to go about this ?

Answer 1

You can use a correlated sub-query:

SELECT task_name,
       ( SELECT LISTAGG(e.name, ',') WITHIN GROUP (
                  ORDER BY INSTR(',' || t.emp_id || ',', ',' || e.id || ',')
                )
         FROM   Employee e
         WHERE  INSTR(',' || t.emp_id || ',', ',' || e.id || ',') > 0
       ) AS emp_names,
       duration,
       wage
FROM   task t

Note: this will output the names in the same order as the id values in your list. If you want to order alphabetically then you can use ORDER BY e.name,

Which, for the sample data:

CREATE TABLE Employee(Id, NAME, Qualification, Experience) AS
SELECT 1, 'Aaron', 'Eng',  2 FROM DUAL UNION ALL
SELECT 2, 'Jacob', 'CS',   3 FROM DUAL UNION ALL
SELECT 3, 'Jehan', 'Art',  1 FROM DUAL UNION ALL
SELECT 4, 'Jenny', 'Math', 7 FROM DUAL;

CREATE TABLE Task (Task_Name, Emp_id, duration, Wage) AS
SELECT 'clean',   '1,3',   200,  50 FROM DUAL UNION ALL
SELECT 'wash',    '4',     300, 200 FROM DUAL UNION ALL
SELECT 'decor',   '1,2,4', 250, 300 FROM DUAL UNION ALL
SELECT 'errands', '2,4',   150, 150 FROM DUAL;

Which outputs:

TASK_NAME	EMP_NAMES	DURATION	WAGE
clean	Aaron,Jehan	200	50
wash	Jenny	300	200
decor	Aaron,Jacob,Jenny	250	300
errands	Jacob,Jenny	150	150

db<>fiddle here

Answer 2

You should not store your id s(int) in "emp_id"(presumably varchar(x)) column. Check out: How to store a list in a column of a database table

You should store each emp_id in another row in your task table to comply first normal form.

create table emp(
id number(10),
name varchar(30),
Qualification varchar(30),
Experience number(10));

create table task(
Task_Name varchar(30),
id number(10),
duration number(10),
wage number(10));

INSERT ALL
   INTO emp VALUES (1,'Aaron','Eng', 2 )
   INTO emp VALUES (2,'Jacob','CS',  3 )
   INTO emp VALUES (3,'Jehan','Art', 1 )
   INTO emp VALUES (4,'Jenny','Math', 7 )
SELECT 1 FROM DUAL;

INSERT ALL
INTO task VALUES('clean', 1, 200, 50 )
INTO task VALUES('clean', 3, 200, 50 )
INTO task VALUES('wash', 4, 300, 200)
INTO task VALUES('decor', 1, 250, 300)
INTO task VALUES('decor', 2, 250, 300)
INTO task VALUES('decor', 4, 250, 300)
INTO task VALUES('errands', 2, 150, 150)
INTO task VALUES('errands', 4, 150, 150)
SELECT 1 FROM DUAL;

After this point joining tables are easy and no need complex functions:

with tab1 as (select * from emp e join task t
on e.id=t.id)
select task_name,
 LISTAGG(e.name, ',') WITHIN GROUP (
                  ORDER BY e.name) name_list, max(duration) duration, max(wage) wage from tab1 e group by task_name;

DB Fiddle:

Answer 3

Umut TEKİN has asked me to write a separate answer in which I build up on their suggestion to change the data model. So, here it is...

You have already been told in the request comments and in Umut's answer that your data model is not good. It violates the first normal form, so to say the base rule on how to build a relational database. Don't store multiple values in a single column. This can make querying the data more complicated and it prevents the DBMS from ensuring data consistency.

You are showing an m:n relation. One task can relate to many employees, and one employee can be involved with many tasks. For an m:n relation you need three tables, in your case:

• employee (employee_id, name, qualification, experience_years)
• task(task_id, task_name, duration_mins, wage)
• task_employee (task_id, employee_id)

The IDs are the tables' primary keys of course and you need two foreign keys in the task_employee table to the other tables.

Using this model doesn't mean that the query you want becomes super-short or extremely simple. But it means that the DBMS can ensure that you cannot combine a task with invalid employee IDs any longer. And the DBMS can probably access the data much quicker, because it can use indexes.

Here is what a query could look like:

select t.task_name, emp.employees, t.duration_mins, t.wage
from task t 
left join
(
  select te.task_id, listagg(e.name, ', ') within group (order by e.name) as employees
  from task_employee te
  join employee e using (employee_id)
  group by te.task_id
) emp using (task_id);