Populate the record links using Recursive CTE

I have the contact table records which has a link of other contact record or the contact record is not linked to anything (null)

As per below example id 21 is a parent for contact 1

enter image description here

I need to populate the temptable using T-SQL records (Using the recursive CTE) with all the contact links for the each and every contact id in contact table as below
As one contact id is associated with multiple contact ids, the Link1,Link2,link3 columns should be dynamically created if possible.

enter image description here

Could anybody please help me with this script

One thought on “Populate the record links using Recursive CTE”

  1. Try this (necessary remarks in comments):
    --data definition
    declare @contactTable table (contactId int, linkContactId int)
    insert into @contactTable values
    --recursive cte
    ;with cte as (
    (select 1 n, contactId from @contactTable
    where linkContactId = 1
    select 1, linkContactId from @contactTable
    where contactId = 1)

    union all
    --this part might seem confusing, I tried writing recursive part similairly as anchor part,
    --but it needed to joins, which isn't allowed in recursive part of cte, so I worked around it
    select n + 1,
    case when cte.n + 1 = t.contactId then t.linkContactId else t.contactId end
    from cte join @contactTable [t] on
    (cte.n + 1 = t.contactId or cte.n + 1 = t.linkContactId)
    --grouping results by contactId concatenating all linkContacts
    select n [contactId],
    (select distinct cast(contactId as varchar(5)) + ',' from cte where n = c.n for xml path(''), type).value('(.)[1]', 'varchar(100)') [linkContactId]
    from cte [c]
    group by n

Leave a Reply

Your email address will not be published.