Using Merge To Combine Matching Records

I'm trying to combine matching records from a table into a single record of another table. I know this can be done with group by, and sum(), max(), etc..., My difficulty is that th

Solution 1:

There's a little known (poorly documented?) aspect of the UPDATE statement when using it to update a @variable which allows you to accumulate/concatenate values in the @variable as part of a set-based UPDATE operation.

This is easier to 'explain' with an example:

createtable source
(account  varchar(10)
,event    varchar(10)
)
go

insert source values ('account1','event 1')
insert source values ('account1','event 2')
insert source values ('account1','event 3')

insert source values ('account2','event 1')

insert source values ('account3','event 1')
insert source values ('account3','event 2')
go

declare@accountvarchar(10),
        @event_list   varchar(40)   -- increase the size to your expected max length select@account='account1'-- allow our UPDATE statement to cycle through the events for 'account1',-- appending each successive event to @event_listupdate  source
set@event_list =@event_list +casewhen@event_list isnotNULLthen' | 'end+ 
                      event
from    source
where   account =@account-- we'll display as a single-row result set; we could also use a 'print' statement ... -- just depends on what format the calling process is looking forselect@accountas account,
        @event_list  as event_list
go

 account    event_list
 ---------- ----------------------------------------
 account1   event 1| event 2| event 3

PRO:

• single UPDATE statement to process a single account value

CON:

• still need a cursor to process a series of account values
• if your desired final output is a single result set then you'll need to store intermediate results (eg, @account and @update) in a (temp) table, then run a final SELECT against this (temp) table to produce the desired result set
• while you're not actually updating the physical table, you may run into problems if you don't have access to 'update' the table

NOTE: You could put the cursor/UPDATE logic in a stored proc, call the proc through a proxy table, and this would allow the output from a series of 'select @account,@update' statements to be returned to the calling process as a single result set ... but that's a whole 'nother topic on a (somewhat) convoluted coding method.

For your process you'll need a cursor to loop through your unique set of account values, but you'll be able to eliminate the cursor overhead for looping through the list of events for a given account. Net result is that you should see some improvement in the time it takes to run your process.

Solution 2:

After applying the given suggestions, and also speaking with our DBA, the winner idea was to ditch the merge and use logical conditions over the loop.

Adding begin/commit seemed to reduce execution time by 1.5 to 3 seconds.

Adding a primary key to the target table did the best reduction, reducing execution time to about 13 seconds.

Converting the merge to conditional logic was the best option in this case, achieving the result in about 8 seconds.

When using conditionals, the primary key in target table is detrimental by a small amount (around 1 sec), but having it drastically reduces time afterwards, since this table is only a previous step for a big join. (That is, the result of this record-merging is latter used in a join with 11+ tables.) So I kept the P.K.

Since there seems to be no solution without a cursor loop, I used the conditionals to merge the values using variables and issuing only the inserts to the target table, thus eliminating the need to seek a record to update or to check its existence.

Here is a simplified example.

createtable #source_t(account varchar(10), event varchar(10));

Insertinto #source_t(account, event) values ('account1','event 1');
Insertinto #source_t(account, event) values ('account1','event 2');
Insertinto #source_t(account, event) values ('account1','event 3');

Insertinto #source_t(account, event) values ('account2','came');
Insertinto #source_t(account, event) values ('account2','saw');
Insertinto #source_t(account, event) values ('account2','conquered');

createtable #target(
    account varchar(10), -- make primary key if the result is to be joined afterwards.
    event_list varchar(2048)
);

declare ciclo cursorforselect account, event
from #source_t c
orderby account --,...for read only;

declare@accountvarchar(10), @eventvarchar(40), @last_account varchar(10), @event_list varchar(1000)

open ciclo

fetch ciclo into@account, @eventset@last_account =@account, @event_list =nullbegin tran

    while @@sqlstatus=0BEGIN 

        if @last_account <>@accountbegin-- if current record's account is different from previous, insert into table the concatenated event string  insertinto #target(account, event_list) values (@last_account, @event_list)        
            set@event_list =null-- Empty the string for the next accountendset@last_account =@account-- Copy current account to the variable that holds the previous oneset@event_list =case@event_list whennullthen@eventelse@event_list +' | '+@eventend-- Concatenate events with separatorfetch ciclo into@account, @eventEND-- after the last fetch, @@sqlstatus changes to <> 0, the values remain in the variables but the loop ends, leaving the last record unprocessed.insertinto #target(account, event_list) values (@last_account, @event_list)

commit tran

close ciclo

deallocatecursor ciclo;

select*from #target;

droptable #target;

droptable #source_t;

Result:

account |event_list                 |
--------|---------------------------|
account1|event1 | event2 | event3|
account2|saw | came | conquered     |

This code worked fast enough in my real use case. However it could be further optimized by filtering the source table to hold only the values que would be necessary for the join afterward. For that matter I saved the final joined resultset (minus the join with #target) in another temp table, leaving some columns blank. Then #source_t was filled using only the accounts present in the resultset, processed it to #target and finally used #target to update the final result. With all that, execution time for production environment was dropped to around 8 seconds (including all steps).

UDF Solutions have solved this kind of problem for me before but in ASE 15 they have to be table-specific and need to write one function per column. Also, that is only possible in development environment, not authorized for production because of read only privileges.

In conclusion, A cursor loop combined with a merge statement is a simple solution for combining records using concatenation of certain values. A primary key or an index including the columns used for the match is required to boost performance.

Conditional logic results in even better performance but comes at the penalty of more complex code (the more you code, the more prone to error).