Showing Multiple rows against an ID in One Row - Oracle SQL

Question

My question is somewhat related to : Display multiple values of a column in one row (SQL Oracle)

However, I could not achieve desired results. Following is my Problem Statement;

I have a SQL Query ;

SELECT initiator_msisdn,  trx_type  || '/'  || SUM(trx_amt/100)  || '/'  || SUM(merchant_comm_amt/100) agent_data
FROM LBI_DM_MK.T_M_INTERNAL_AUDIT_D
WHERE DATA_DATE = '20180401'
AND trx_status  ='Completed'
GROUP BY initiator_msisdn,  trx_type
;

That returns these rows;

The SQL That brings this data is ;

But, I want following result.

Please help to sort out this issue;

Answer 1

You could use LISTAGG:

WITH cte AS (
  SELECT initiator_msisdn,
          trx_type  || '/'  || SUM(trx_amt/100)  || '/'  ||
          SUM(merchant_comm_amt/100) agent_data
  FROM LBI_DM_MK.T_M_INTERNAL_AUDIT_D
  WHERE DATA_DATE = '20180401'
    AND trx_status  ='Completed'
  GROUP BY initiator_msisdn,  trx_type
)
SELECT initiator_msisdn,
       LISTAGG(agent_data, '|') WITHIN GROUP (ORDER BY agent_data) AS agent_data
FROM cte
GROUP BY initiator_msisdn;