How To Merge Rows Of Sql Data On Column-based Logic?

I'm writing a margin report on our General Ledger and I've got the basics working, but I need to merge the rows based on specific logic and I don't know how... My data looks like t

Solution 1:

Try this:

SELECT RIGHT(LTRIM(RTRIM(value1)),3) , value2, MAX(location), MAX(date), MAX(category), SUM(debitamount), SUM(creditamount) FROM table1 GROUP BY RIGHT(LTRIM(RTRIM(value1)),3), value2

It will sum the credit amount and debit amount. It will choose the maximum string value in the other columns, assuming they are always the same when value2 and the last 3 digits of value1 are the same it shouldn't matter.