SQL query to get / delimited column value in single row

I have this output:

Table 1

Requisition_number	per_id	per_name	Job_title	Interview	TAS_EMAIL_ADDRESS	TAS_FNAME
22021	1097	Chad	Manager	This is a comment	[e-mail]	abc
22021	1097	Chad	Manager	This is a comment	[e-mail]	xyz

I want the output to look like this:

Requisition_number	per_id	per_name	Job_title	Interview	TAS_EMAIL_ADDRESS	TAS_FNAME
22021	1097	Chad	Manager	This is a comment	[e-mail]	abc/xyz
22021	1097	Chad	Manager	This is a comment	[e-mail]

i.e for the same requistion_number for the same per_id, if all other columns are same except the TAS_EMAIL_ADDRESS, I want to concatenate the

TAS_FNAME - abc/xyz 

and repeat in the rows.

I am using this expression in my query:

rtrim (xmlagg (xmlelement(e,tas_fname||'/')).extract ('//text()'), '/') AS tas_fname

but this is not working and giving the same output like table 1.

listagg function is throwing a syntax error when I am using it with within group (order by)

Answer

You can use LISTAGG as an analytic function:

SELECT Requisition_number,
       per_id,
       per_name,
       Job_title,
       Interview,
       TAS_EMAIL_ADDRESS, 
       CASE ROW_NUMBER()
            OVER (
              PARTITION BY Requisition_number,
                           per_id,
                           per_name,
                           Job_title,
                           Interview
              ORDER     BY tas_fname
            )
       WHEN 1
       THEN LISTAGG(tas_fname, '/') WITHIN GROUP (ORDER BY tas_fname)
            OVER (
              PARTITION BY Requisition_number,
                           per_id,
                           per_name,
                           Job_title,
                           Interview
            )
       END AS tas_fname
FROM   table_name

Which, for the sample dataL

CREATE TABLE table_name (Requisition_number, per_id, per_name, Job_title, Interview, TAS_EMAIL_ADDRESS, TAS_FNAME) AS
SELECT 22021, 1097, 'Chad', 'Manager', 'This is a comment', '[e-mail]', 'abc' FROM DUAL UNION ALL
SELECT 22021, 1097, 'Chad', 'Manager', 'This is a comment', '[e-mail]', 'xyz' FROM DUAL;

Outputs:

REQUISITION_NUMBER	PER_ID	PER_NAME	JOB_TITLE	INTERVIEW	TAS_EMAIL_ADDRESS	TAS_FNAME
22021	1097	Chad	Manager	This is a comment	[e-mail]	abc/xyz
22021	1097	Chad	Manager	This is a comment	[e-mail]	null

db<>fiddle here