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.
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?
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.