Updating Table From Another Table With Row Mismatch

I currently work with financial software based, mainly, in stored procedures in a few Microsoft SQL Server databases.  A lot of my efforts are directed toward maintaining the current processes while integrating enhancements and modifications whenever possible.

Recently, I ran into a problem with an UPDATE statement.  Here is the way the update process was built:

with CTE_1 as (...),
CTE_2 as (...)
    update TargetTable
    set
        Value_1 = case c.TransType when 1 then c.ComputedAmt else Value_1,
        Value_2 = case c.TransType when 2 then c.ComputedAmt else Value_2            
    from
        CTE_x c
    where
        TargetTable.Key = c.Key

My intention was simple: update one column when the transaction type was one value, and update another column when the transaction type was another value.  But, it failed.  Let's take a look at the data generated in the CTE section for table CTE_x (that being either CTE_1 or CTE_2):

Key	TransType	ComputedAmt
ABC01	1	123.45
ABC01	2	989.02
EFG33	1	34.00
EFG33	2	3024.63

...

Now take a look at how TargetTable is populated:

Key	Value_1	Value_2
ABC01	0.00	0.00
EFG33	0.00	0.00

...

The problem with the UPDATE statement above is that I want to update each row in the target table twice.  This is forbidden.

The solution was to add another CTE that took the data from CTE_x and pivoted it so that I would have a one-to-one relationship between the target table and the source of the updates:

with ...
,
CTE_pivot as (
        select
            Key as "SourceKey",
            [1] as "VAL_a",[2] as "VAL_b"
        from (select Key,TransType,ComputedAmt from CTE_x) as x
        pivot (
            max(ComputedAmt)
            for TransType in ([1],[2])
        ) as pvt
)

With the pivoted data now in CTE_pivot, the data was correctly organized thusly:

Key	VAL_a	VAL_b
ABC01	123.45	989.02
EFG33	34.00	3024.63

...


And with this, the final UPDATE statement gets a single row with both columns from the source table (now CTE_pivot):

with ...
update TargetTable
  set Value_1 = VAL_a, Value_2 = VAL_b
from
  CTE_pivot
where
  CTE_pivot.Key = TargetTable.Key