How to find ultimate parent

24 Views Asked by At

I have dataset like this

drop table if exists #dataset

create table #dataset(CIF VARCHAR(255), PARENT_CIF VARCHAR(255), UP_CIF VARCHAR(255))

INSERT INTO #dataset

SELECT 45218547, 54044082, 54044082 union all

SELECT 54044082, 64349595, 64349595 union all

SELECT 59741694, 54044082, 54044082 union all

SELECT 64349595, 59741694, 59741694 union all

SELECT 189, 345978, 345978 union all

SELECT 22002363, 189, 189 union all

SELECT 28459008, 22002363, 22002363 union all

SELECT 29135817, 28459008, 28459008 union all

SELECT 29154411, 29135817, 29135817

expected result set

SELECT 45218547, 54044082, 64349595 union all SELECT 54044082, 64349595, 64349595 union all SELECT 59741694, 54044082, 64349595 union all SELECT 64349595, 59741694, 64349595 union all SELECT 189, 345978, 345978 union all SELECT 22002363, 189, 345978 union all SELECT 28459008, 22002363, 345978 union all SELECT 29135817, 28459008, 345978 union all SELECT 29154411, 29135817, 345978

how to achieve this with TSQL

0

There are 0 best solutions below