By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Requests
(RequestID int,
Value1Old int,
Value1New int,
Value2Old int,
Value2New int);
INSERT INTO Requests
(RequestID, Value1Old, Value1New, Value2Old, Value2New)
VALUES
(1, NULL, 50, NULL, NULL),
(2, 50, 100, NULL, 100),
(3, 100, 200, 100, 300),
(4, NULL, NULL, 300, 500),
(5, 200, 0, 600, 1000),
(6, NULL, NULL, 1000, 0);
6 rows affected
create function fnChange (@Old int, @New int)
returns varchar(10) as
begin
return (
select
case
when IsNull(@Old,0)=IsNull(@New,0) then null
else
case when @New=0 or @new is null then 'Delete' else
case when @Old is null then 'Insert'
else 'Amend' end
end
end
)
end
with r as (
select RequestId, dbo.fnChange(Value1Old,Value1New) Change, Value1Old, Value1New, null Value2Old, null Value2New
from Requests
union all
select RequestId, dbo.fnChange(Value2Old,Value2New) Change, null Value1Old, null Value1New, Value2Old, Value2New
from Requests
)
select requestId, Change,
Max(Value1Old) Value1Old, Max(Value1New) value1New,
Max(Value2Old) Value2Old, Max(Value2New) value2New
from r where change is not null
group by requestId, Change
order by requestId
| requestId | Change | Value1Old | value1New | Value2Old | value2New |
|---|---|---|---|---|---|
| 1 | Insert | null | 50 | null | null |
| 2 | Amend | 50 | 100 | null | null |
| 2 | Insert | null | null | null | 100 |
| 3 | Amend | 100 | 200 | 100 | 300 |
| 4 | Amend | null | null | 300 | 500 |
| 5 | Amend | null | null | 600 | 1000 |
| 5 | Delete | 200 | 0 | null | null |
| 6 | Delete | null | null | 1000 | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.