Show multiple rows of a SQL query in a single row (comma separated)

Suppose If you have a SQL query which returns multiple rows when you join 2 tables, as the second table has multiple records for the same joining column of the first table.

In this case you can use the inbuilt XMLAGG function available in SQL.

Table 1 -> TXN_PROC_OPN

Table 2 -> TXN_PROC_REMARK_M

SQL Query ->

SELECT 
           TPO.WORKITEMID, 
           TRIM(',' FROM XMLAGG(XMLELEMENT(E,PRM.REMARKS || ',' ) ORDER BY                                                              PRM.WORKITEMID).EXTRACT('//text()')) AS MAKER_REMARKS
FROM
           TXN_PROC_OPN TPO, TXN_PROC_REMARK_M PRM
WHERE
           TPO.WORKITEMID = PRM.WORKITEMID
GROUP BY
           TPO.WORKITEMID


Output->