How to Loop and transform this linking table?

Transact-SQL
1

Hi All I urgently need help with this SQL query.

I have one SQL table, (I didn't build this so don't ask me why it's structured like this) but I need to urgently extract data and I'm stuck on this step.

So basically there is one Table in the database it has 3 columns.
ID, NextID, comments.

Comments column isn't used for anything.

This table shows the linkage that NextID is next in sequence after this ID.

What SQL will allow me to create a new table that maps all the IDs from the same "Group" in the sequence.

e.g. Here is some sample data:

ID, NextID, comments.
1, 5,
5, 11
6, 8
11, 26
22, 37

so 1, 5, 11, 26 are from the same family (same sequence) because they follow each other.
because 1 goes to 5, then 5 goes to 11, 11 goes to 26.
6, 8 are one Group.
22, 37 are one Group.

the sequence could be up to 10 links I guess or longer maybe.

The result table I wish to have is:
GroupID, ID
A,1
A,5
A,11
A,26
B,6
B,8
C,22
C, 37

ideally those that don't have links, just by themselves also get a groupID . Like ID 2, 3, 4, 7, 9, 10 etc are just single and nothing follows them in the sequence.

Thanks in advance for your help. I'm using Microsoft SQL Studio. SQL Server 13.0.5492.2

2

Welcome

What happens when the grouping passes Z?

Can the grouping be numeric?

3

Yes, the grouping can be numeric. it can even just be the original ID value. (For the start of the sequence). That's actually better than alphabet letters.

1,1
1,5
1,11
1,26
6,6
6,8
22,22
22, 37

4

hi
my feeble attempt
hope this helps ...

please click arrow for drop create data

drop table #temp

create table #temp
(
id int ,
nextid int
)

insert into #temp select 1, 5
insert into #temp select 5, 11
insert into #temp select 6, 8
insert into #temp select 11, 26
insert into #temp select 22, 37

go

; with cte as 
(
select a.* from #temp a join #temp b on a.nextid  = b.id 
union 
select b.* from #temp a join #temp b on a.nextid  = b.id 
)
 select id from cte 
 union 
 select nextid from cte

image

5

Hi harishgg1,

Can you please explain how it works? Will this work if the table has very long list of many hundreds of different sequences.

The results returned is only 1 column? I need results that has atleast 2 columns I think because I need to know the IDs that map to the original ID.

6

what about s situation like this ?

image

7

that wouldn't happen.

If 11 follows 5. Then it's impossible for 3 to follow after 5.

Same for 28 , 5 if 5 follows 1 then it can't follow 28.

Think of them as photo copy of the original.

I need to be able to somehow group them as the same ID. that is the original ID.

8 
; with cte as 
(
select id from #temp 
union all 
select nextid from #temp 
), cte_1 as 
(
select id,count(*) as cnt from cte group by id having count(*) = 2 
) , cte_grp as 
(
select case when a.id = b.id or a.nextid = b.id then 1 end as grp,a.* from #temp a left join cte_1 b on a.id = b.id or a.nextid = b.id 
) 
select distinct * from cte_grp where grp is not null

image