Convert the Column To Row

SELECT      (SELECT   'Manager Note - '||APPROVER_COMMENTS
               FROM   < Table Name >
              WHERE   approval_seq = 1 AND leave_req_id = :p_leave_req_id)
         || ' '
         || (SELECT  'HR Note - ' || APPROVER_COMMENTS
               FROM   < Table Name >               WHERE   approval_seq = 2 AND leave_req_id = :p_leave_req_id) mana_hr_note
--  INTO   l_man_hr_note
  FROM   DUAL;
 
   
 
select user_id,listagg (login_id, ',') WITHIN GROUP (ORDER BY login_id) login_id from (select user_id,login_id from fnd_logins
where SESSION_NUMBER < (200)
and user_id in (1179,1178)
) group by user_id

select listagg (APPROVER_COMMENTS, ',') WITHIN GROUP (ORDER BY APPROVER_COMMENTS) login_id
from < Table Name >   where leave_req_id = p_leave_req_id

select listagg (APPROVER_COMMENTS, ',') WITHIN GROUP (ORDER BY approval_seq) login_id
from < Table Name >where leave_req_id = :p_leave_req_id