Parent Child Matrix

Database
Visitor

Parent Child Matrix

I am trying to create the below matrix based on the sample data set.

Can this be done with SQL or does a stored proc needs to be written?

Thanks for help in advance.

 

DataSet

old_id,new_id
old_id,new_id
1,2
2,3
3,4
2,10
10,20
15,21
21,22
22,23
23,24
24,25


Expected Matrix
1,4
2,4
3,4
2,20
10,20
15,25
21,25
22,25
23,25
24,25

2 REPLIES
Junior Contributor

Re: Parent Child Matrix

This depends on your data:

Are there circles like 1-2, 2-1 or 1-2, 2-3, 3-1? 

Are ids unique?

 

If this is your actual data & expected result, what are the rules to assign those rows to two different groups instead of one?

1,4
2,4
3,4
2,20
10,20

Supporter

Re: Parent Child Matrix

Hi Amit,

 

I suppose you are looking to find the newest ID possible for the old id's. But I see a hole in your data set. If you see 2nd and 4th row both have 2 as old id and the new id's is 3 and 10 respectively. If we go by the 4th row route the new id can be considered as 20 for 2. So my question is the sequence of the rows defined. In your example I will consider rows (1,2,3) as one group , (2,10) as 2nd and remaining as third. Then you should consider adding a ORDER column to your data for maintaining the sequence. Based on that we could come with a query.

 

Thanks,

Rohan Sawant