My source table looks like this:
Account_Number, Code, Transaction_Amount, Comment, Sequence_Number
4321, W, 10.21,,1
4321, C,,'Payment',2
4321, C,,'From Checking Account',3
4321, D,20.00,,4
4321, C,,'Direct Deposit',4
7430, W, 40.00,,5
7430, C,,'From Checking',5
the source is a transaction ledger, W stands for "Withdraw", D stands for "Deposit", and C stand for comment. when every transaction occurs, it could be follow by. 0 or more comment lines. I would like to aggregate all the comments that comes after a "W" or a "D" and add them to the "W" or "D" row. Example:
Account_Number, Code, Transaction_Amount, Comment, Sequence_Number
4321, W, 10.21,'Payment,From Checking Account',1
4321, D,20.00,'Direct Deposit',4
7430, W, 40.00,"From Checking,5
Here is the Query using the
LEAD
andSTRING_AGG
. Assuming some big number for the lastWithdraw
orDeposit
row for aAccount_Number
.HERE is the Working Code.
Results: