Is there a way to copy and coalesce entire rows in SQL and assign them new values for a single linking ID column?

Question

I know there is this question but I think my scenario is a bit different...

So I have a view vw_BindingDataCurrent, and it contains a BindingID, a CaseID, a bunch of other IDs (PersonID, OrganizationID, ServiceID, and a few others), and some other columns containing data associated with the binding. Now there is a unique constraint on the IDs in the backing table for the view apart from BindingID, so no duplicate rows can be created that have the same values for all those other IDs.

My issue is I want to search this view for rows that match one of two CaseIDs and copy them, giving them a third @CaseIDCharlie. Basically I'm merging data for two cases into one.

Here's some code that illustrates the situation:

declare @merge table
(
    id int
)
declare @binding table
(
    id int identity,
    caseid int,
    personid int,
    organizationid int,
    serviceid int,
    durationid int,
    specialistid int,
    noteid int,
    dummy nvarchar(max)
)


insert into @binding
    (caseid, personid, organizationid, serviceid, durationid, specialistid, noteid, dummy)
values
    (1, 42, 42, 42, 42, 42, 42, 'overwrite me'), -- this should be overwritten
    (1, 86, 42, 42, 42, 42, 42, 'good data'), -- this should appear
    (2, 42, 42, 42, 42, 42, 42, 'good data'), -- this should appear
    (3, 42, 42, 42, 42, 42, 42, 'wrong case') -- this is a different case ID, should not appear

-- copy bindings to new case
delete @merge
insert into @merge select id from @binding where CaseID in (1,2) -- need to filter out duplicates by the other IDs
select b.* from @merge m join @binding b on m.id = b.id

What I want is for the @merge table to contain only the IDs of the second and third rows from the @binding table. The first row should be skipped because it is a duplicate of the third row, and the fourth row should be skipped because it is associated with case ID number 3, which is not one of the two case IDs we are merging.

Or, in other words, you could say it like this: I want something like select distinct personid, organizationid, serviceid, durationid, specialistid, noteid from @binding but then somehow I would get the id and dummy fields as well from the record for case ID 2, even though they are not the same between cases 1 and 2...

Answer 1

IIUC - consider an aggregate query grouping by all ids and take the max id:

insert into @merge 
select max(id)
from @binding
where caseid in (1,2)
group by personid, organizationid, serviceid, durationid, specialistid, noteid

Output

|    | id | caseid | personid | organizationid | serviceid | durationid | specialistid | noteid | dummy     |
|----|----|--------|----------|----------------|-----------|------------|--------------|--------|-----------|
| 1  | 2  | 1      | 86       | 42             | 42        | 42         | 42           | 42     | good data |
| 2  | 3  | 2      | 42       | 42             | 42        | 42         | 42           | 42     | good data |

Answer 2

It depends on what you consider the order of rows to be for the binding table.

The typical method is to use row_number(), then order by whatever parameters you want:

    insert into @merge 
    select id from 
    (
    select id, caseID,
      row_number() over(partition by caseid order by id desc) AS RowNr  
   --can define order by as necessary to get the most recent row
    from @binding 
    ) A
    where (RowNr = 1)
      and (CaseID in (1,2))

    select b.* from @merge m join @binding b on m.id = b.id

If all you want is to sort by the order of loading into the @Binding table then max(ID) will work.