Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE emp (
   name varchar2(100) NOT NULL,
   employee_id varchar2(100) NOT NULL,
   employee_allowance number not null
);
BEGIN
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('Bob', '11Bob923', 13);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('Bob', '11Bob532', 13);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('Sara', '12Sara833', 93);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('John', '18John243', 21);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('John', '18John243', 21);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('John', '18John823', 43);
END;
/
1 rows affected

SELECT name,
       LISTAGG(employee_id, ', ')  WITHIN GROUP (ORDER BY employee_id) AS employee_ids,
       SUM(employee_allowance) AS allowance
FROM   (
  SELECT DISTINCT *
  FROM   emp
)
GROUP BY name


NAME
EMPLOYEE_IDS
ALLOWANCE


Bob
11Bob532, 11Bob923
26

John
18John243, 18John823
64

Sara
12Sara833
93

NAME	EMPLOYEE_IDS	ALLOWANCE
Bob	11Bob532, 11Bob923	26
John	18John243, 18John823	64
Sara	12Sara833	93