Replacing comma separated integers in Oracle-CodePudding

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 ?

CodePudding user response：

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

CodePudding user response：

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: