SQL revises table data into a more compact form
I have a table with data pairs, modeled like this:
Id1 Id2
-----------
100 50
120 70
70 50
34 20
50 40
40 10
Id1
always more than Id2
. These pairs represent substitutions that must be performed. So 100 will be replaced with 50, but then 50 will be replaced with 40, which will then be replaced with 10.
So the result would be:
Id1 Id2
-----------
100 10
120 10
34 20
Is there a nice concise way that I can change, or join this table to represent this?
I know I can attach something similar to it:
SELECT t1.Id1, t2.Id2
FROM mytable t1
JOIN myTable t2 ON t2.Id1 = t1.Id2
But this will take multiple passes, so why am I asking if there is a better way to accomplish this?
+3
source to share
1 answer
declare @t table(Id1 int, Id2 int)
insert @t values (100, 50)
insert @t values ( 120, 70)
insert @t values ( 70, 50)
insert @t values ( 34, 20)
insert @t values ( 50, 40)
insert @t values ( 40, 10)
;with a as
(
-- find all rows without parent <*>
select id2, id1 from @t t where not exists (select 1 from @t where t.id1 = id2)
union all -- recusive work down to lowest child while storing the parent id1
select t.id2 , a.id1
from a
join @t t on a.id2 = t.id1
)
-- show the lowest child for each row found in <*>
select id1, min(id2) id2 from a
group by id1
Result:
id1 id2
----------- -----------
34 20
100 10
120 10
+3
source to share