By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table t
(id int identity ,item int, item_analog int)
insert into t values
(2 ,10),
(3 ,11),
(2 ,11),
(11 ,7),
(8 ,2),
(7 ,4),
(6 ,9);
create table t1(iteration int, item int, previtem int)
truncate table t1
declare @anchor int
set @anchor = 11
insert into t1 values (1,@anchor,0)
declare @iteration int
set @iteration = 2
insert into t1
select @iteration,
case when t.item = t1.item then t.item_analog else t.item end,
t1.item
from t1
join t on (t1.item = t.item) or (t1.item = t.item_analog)
where t1.iteration = @iteration - 1
--select * from t1
delete from t1 where iteration = 3
set @iteration = 3
insert into t1
| iteration | item | previtem |
|---|---|---|
| 1 | 11 | 0 |
| 2 | 3 | 11 |
| 2 | 2 | 11 |
| 2 | 7 | 11 |
| 3 | 10 | 2 |
| 3 | 8 | 2 |
| 3 | 4 | 7 |
| item | item_analog |
|---|---|
| 2 | 10 |
| 3 | 11 |
| 2 | 11 |
| 11 | 7 |
| 8 | 2 |
| 7 | 4 |
| (No column name) |
|---|
| 3 |