Following SQL gave me the correct result but not desirable format, how can I modify my format so I can have the lay out I want?
Select HSP_ACCOUNT_ID, PAYOR_ID, sum(tx_amount) from hsp_transactions
Where HSP_ACCOUNT_ID=70000958 and (TX_TYPE_HA_C =3 or TX_TYPE_HA_C =4)
Group by HSP_ACCOUNT_ID, PAYOR_ID, TX_TYPE_HA_C;
HSP_ACCOUNT_ID PAYOR_ID SUM(TX_AMOUNT)
70000958 1021 - 476
70000958 1088 -32533.63
70000958 1058 -257.65
Desirable result is one row, if there are three payor, then one row list all three payor by the order of smallest amount to biggest amount.
HSP_ACCOUNT_ID,PAYOR_ID1,SUM(TX_AMOUNT)1,PAYOR_ID2,SUM(TX_AMOUNT)2,PAYOR_ID3,SUM(TX_AMOUNT)3
70000958,1088, -32533.63, 1021, -476, 1058, -257.65
Select HSP_ACCOUNT_ID, PAYOR_ID, sum(tx_amount) from hsp_transactions
Where HSP_ACCOUNT_ID=70000958 and (TX_TYPE_HA_C =3 or TX_TYPE_HA_C =4)
Group by HSP_ACCOUNT_ID, PAYOR_ID, TX_TYPE_HA_C;
HSP_ACCOUNT_ID PAYOR_ID SUM(TX_AMOUNT)
70000958 1021 - 476
70000958 1088 -32533.63
70000958 1058 -257.65
Desirable result is one row, if there are three payor, then one row list all three payor by the order of smallest amount to biggest amount.
HSP_ACCOUNT_ID,PAYOR_ID1,SUM(TX_AMOUNT)1,PAYOR_ID2,SUM(TX_AMOUNT)2,PAYOR_ID3,SUM(TX_AMOUNT)3
70000958,1088, -32533.63, 1021, -476, 1058, -257.65