sql server - How to merge rows of SQL data on column-based logic? -

sql server - How to merge rows of SQL data on column-based logic? -

i'm writing margin study on our general ledger , i've got basics working, need merge rows based on specific logic , don't know how...

my info looks this:

value1 value2 location date category debitamount creditamount 2029 390 deed 2012-07-29 costs - widgets , gadgets 0.000 3.385 3029 390 deed 2012-07-24 sales - widgets , gadgets 1.170 0.000

and study needs display 2 columns so:

plant date category debitamount creditamount deed 2012-07-29 widgets , gadgets 1.170 3.385

the logic bring together them contained in value1 , value 2 column. lastly 3 digits of value 1 , 3 digits of value 2 same, rows should combined. also, 1st digit of value 1 been 2 sales , 3 costs (not sure if matters)

ie 2029-390 money coming in widgets , gadgets sold customers, while 3029-390 money beingness spent purchase widgets , gadgets suppliers.

how can programmatically in stored procedure? (sql server 2008 r2)

edit: load 3000's 1 variable table , 2000's another, bring together 2 on value2 , right(value1, 3)? or that?

try this:

select right(ltrim(rtrim(value1)),3) , value2, max(location), max(date), max(category), sum(debitamount), sum(creditamount) table1 grouping right(ltrim(rtrim(value1)),3), value2

it sum credit amount , debit amount. take maximum string value in other columns, assuming same when value2 , lastly 3 digits of value1 same shouldn't matter.

sql-server tsql reporting