Swap value between rows

I want to swap dynamically values in column VAL2.

The case is when in tech I have got X. I want to upload data from other rows where values in cells are the same ( the only difference is EN1 and EN2 ) because values there are reversed.

~~ SQL server

I tried to update, case, but I do not know how to pass only one value in the update and make it dynamically for many different cases.

UPDATE XX
SET VAL2= (select distinct VAL2 from xx where 
select EN1,EN2....

where TECH = 'X'

Answer

declare @xx table
(
[Year] varchar(10),
EN1 varchar(5),
EN2 varchar(5),
P varchar(5),
M varchar(5),
CC1 varchar(5),
VAL1 decimal(18,9),
VAL2 decimal(18,9),
TECH varchar(5)
);

insert into @xx
values
('2021BUD', 'E1', 'E2', 'P1', 'M1', 'CC1', 1, 2, ''),
('2021BUD', 'E2', 'E1', 'P1', 'M1', 'CC1', 3, 4, 'X'),
('2021BUD', 'E3', 'E4', 'P1', 'M1', 'CC1', 5, 6, ''),
('2021BUD', 'E4', 'E3', 'P1', 'M1', 'CC1', 7, 8, 'X'),
--duplicates
('2021BUD', 'E5', 'E6', 'P1', 'M1', 'CC1', 9, 10, ''),
('2021BUD', 'E5', 'E6', 'P1', 'M1', 'CC1', 99, 1010, ''),
('2021BUD', 'E6', 'E5', 'P1', 'M1', 'CC1', 11, 12, 'X'),

('2021BUD', 'E7', 'E8', 'P1', 'M1', 'CC1', 13, 14, ''),
('2021BUD', 'E8', 'E7', 'P1', 'M1', 'CC1', 15, 16, 'X'),
('2021BUD', 'E8', 'E7', 'P1', 'M1', 'CC1', 17, 18, 'X');


select *
from @xx;


--A
with cte
as
(
select t.EN1, t.EN2, t.VAL2, m.VAL2 as swapVAL2, count(case when t.TECH in ('', 'X') then 1 end) over(partition by t.EN1, t.EN2) as cnt
from @xx as t 
join @xx as m on t.Year=m.Year and t.EN1 = m.EN2 and t.EN2 = m.EN1 and t.P = m.P and t.M = m.M and t.CC1 = m.CC1
)
update cte
set VAL2 = swapVAL2
where cnt=1;

select *
from @xx;

go

--or, no check for duplicates etc, all Tech{empty}rows have a single corresponding TechX row

declare @yy table
(
[Year] varchar(10),
EN1 varchar(5),
EN2 varchar(5),
P varchar(5),
M varchar(5),
CC1 varchar(5),
VAL1 decimal(18,9),
VAL2 decimal(18,9),
TECH varchar(5)
);

insert into @yy
values
('2021BUD', 'E1', 'E2', 'P1', 'M1', 'CC1', 1, 2, ''),
('2021BUD', 'E2', 'E1', 'P1', 'M1', 'CC1', 3, 4, 'X'),
('2021BUD', 'E3', 'E4', 'P1', 'M1', 'CC1', 5, 6, ''),
('2021BUD', 'E4', 'E3', 'P1', 'M1', 'CC1', 7, 8, 'X');

select * from @yy;

/*
select *, 
    max(case when TECH = 'X' then val2 end) over(partition by Year, P, M, CC1, case when EN1 > EN2 then EN2 else EN1 end, case when EN1>EN2 then EN1 else EN2 end) as TechXVal2,
    max(case when TECH = '' then val2 end) over(partition by Year, P, M, CC1, case when EN1 > EN2 then EN2 else EN1 end, case when EN1>EN2 then EN1 else EN2 end) as TechVal2
from @yy
*/

with y
as
(
select VAL2, TECH, 
    max(case when TECH = 'X' then val2 end) over(partition by Year, P, M, CC1, case when EN1 > EN2 then EN2 else EN1 end, case when EN1 > EN2 then EN1 else EN2 end) as TechXVal2,
    max(case when TECH = ''  then val2 end) over(partition by Year, P, M, CC1, case when EN1 > EN2 then EN2 else EN1 end, case when EN1 > EN2 then EN1 else EN2 end) as TechVal2
from @yy
)
update y
--techX gets the VAL2 of tech and tech gets VAL2 of techX
set VAL2 = case TECH when 'X' then TechVal2 else TechXVal2 end;


select *
from @yy;
go