Migrating data from using ManagerId to HierarchyId

  • I have a table called Person with the following schema:

    CREATE TABLE [People].[Person](
    [Id] [smallint] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [LineManagerId] [smallint] NULL,
    [OrganisationLevel] [hierarchyid] NULL
    ) ON [PRIMARY]
    GO

    LineManagerId is a FK to the People.Person table to identify the person in the table who is the Person's Line Manager.

    I need to populate the [OrganisationLevel] column to model the hierarcy of the company.

    I can identify the top managers as those where [LineManagerId] IS NULL and seed them with the first level thus:

    WITH TopManagers AS
    (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY Id ASC) AS RowNum,
    Id 
    FROM 
    [People].[Person] 
    WHERE 
    (LineManagerId IS NULL)
    )

    UPDATE
    [People].[Person] 
    SET
    OrganisationLevel = '/'+CAST(TopManagers.RowNum AS varchar)+'/'
    FROM
    [People].[Person] INNER JOIN
    TopManagers ON Person.Id = TopManagers.Id;


    So I can now see the [OrganisationLevel] for the top managers:

    SELECT 
       OrganisationLevel,
       OrganisationLevel.ToString() AS OrgLevel
    FROM
       [People].[Person]
    WHERE
       (LineManagerId IS NULL)
    OrganisationLevel   OrgLevel
    0x58 /1/
    0x68 /2/
    0x78 /3/

    How can I now go about recursively setting the OrganisationLevel for all remaining employees based on their LineManagerId.  For example, for each of the three Top Managers in the recordset above, they will have direct employees for whom they are line managers, and those will have a further set.  Is it possible to do this without using a cursor?

     

     

     

  • Nice question, but how about also giving us some consumable sample data to test with?


  • Here's some data inexpertly obfuscated.

    Attachments:
    You must be logged in to view attached files.
  • Also inexpertly provided. In future, please help others by providing your data in consumable format ... ie, one which can be pasted straight into SSMS. With nearly 6,000 points, you should know this already.

    DROP TABLE IF EXISTS #Person;

    CREATE TABLE #Person
    (
    Id SMALLINT NOT NULL PRIMARY KEY
    ,FirstName NVARCHAR(50) NOT NULL
    ,LastName NVARCHAR(50) NOT NULL
    ,LineManagerId SMALLINT NULL
    ,OrganisationLevel HIERARCHYID NULL
    );

    INSERT #Person
    (
    Id
    ,FirstName
    ,LastName
    ,LineManagerId
    )
    VALUES
    (1, 'Adaxxxxxx', 'Chmxxxxxxki', 15)
    ,(2, 'Aisxxxxxx', 'Ikrxxxxxx', 19)
    ,(3, 'Alexxxxxx', 'Jacxxxxxx', 36)
    ,(4, 'Alixxxxxx', 'Smixxxxxx', 48)
    ,(5, 'Andxxxxxx', 'Berxxxxxx', 15)
    ,(6, 'Andxxxxxx', 'Caixxxxxx', 24)
    ,(7, 'Antxxxxxx', 'Barxxxxxx', 6)
    ,(8, 'Antxxxxxx', 'Barxxxxxx', 24)
    ,(9, 'Ashxxxxxx', 'Robxxxxxx', 19)
    ,(10, 'Chaxxxxxx', 'Macxxxxxx', 133)
    ,(11, 'Craxxxxxx', 'Salxxxxxx', 19)
    ,(12, 'Dalxxxxxx', 'Danxxxxxx', 110)
    ,(13, 'Damxxxxxx', 'Kelxxxxxx', 32)
    ,(14, 'Edwxxxxxx', 'Colxxxxxx', 46)
    ,(15, 'Glexxxxxx', 'Croxxxxxx', 19)
    ,(16, 'Jamxxxxxx', 'Johxxxxxx', 48)
    ,(17, 'Jonxxxxxx', 'Macxxxxxx', 19)
    ,(18, 'Jonxxxxxx', 'Horxxxxxx', 32)
    ,(19, 'Julxxxxxx', 'Madxxxxxx', 25)
    ,(20, 'Kasxxxxxx', 'Yonxxxxxx', 6)
    ,(21, 'Katxxxxxx', 'Cooxxxxxx', 32)
    ,(22, 'Laxxxxxxx', 'Konxxxxxx', 46)
    ,(23, 'Leaxxxxxx', 'Benxxxxxx', 17)
    ,(24, 'Levxxxxxx', 'Ncuxxxxxx', NULL)
    ,(25, 'Matxxxxxx', 'Allxxxxxx', 17)
    ,(26, 'Matxxxxxx', 'Armxxxxxx', 19)
    ,(27, 'Micxxxxxx', 'Jusxxxxxx', 19)
    ,(28, 'Mikxxxxxx', 'Emmxxxxxx', 48)
    ,(29, 'Morxxxxxx', 'Penxxxxxx', 19)
    ,(30, 'Muhxxxxxx', 'Mahxxxxxx', 32)
    ,(31, 'Navxxxxxx', 'Afsxxxxxx', 110)
    ,(32, 'Nitxxxxxx', 'Gupxxxxxx', 17)
    ,(33, 'Olixxxxxx', 'Parxxxxxx', 48)
    ,(34, 'Pooxxxxxx', 'Sarxxxxxx', 8)
    ,(35, 'Prexxxxxx', 'Panxxxxxx', 17)
    ,(36, 'Prixxxxxx', 'Potxxxxxx', 9)
    ,(37, 'Rahxxxxxx', 'Rahxxxxxx', 6)
    ,(38, 'Ramxxxxxx', 'Turxxxxxx', 15)
    ,(39, 'Ryaxxxxxx', 'Trexxxxxx', 8)
    ,(40, 'Salxxxxxx', 'Shaxxxxxx', 110)
    ,(41, 'Samxxxxxx', 'Danxxxxxx', 25)
    ,(42, 'Scoxxxxxx', 'Davxxxxxx', 8)
    ,(43, 'Scoxxxxxx', 'Robxxxxxx', 19)
    ,(44, 'Stexxxxxx', 'Mawxxxxxx', 32)
    ,(45, 'Surxxxxxx', 'Thaxxxxxx', 17)
    ,(46, 'Tarxxxxxx', 'Frexxxxxx', 24)
    ,(47, 'Thoxxxxxx', 'Bowxxxxxx', 8)
    ,(48, 'Timxxxxxx', 'Butxxxxxx', 24)
    ,(49, 'Xhexxxxxx', 'Canxxxxxx', 46)
    ,(50, 'Stuxxxxxx', 'Quixxxxxx', 19)
    ,(51, 'Lauxxxxxx', 'Nauxxxxxx', 110)
    ,(52, 'Nkoxxxxxxi', 'Kozxxxxxx', 8)
    ,(53, 'Shwxxxxxx', 'Dhaxxxxxxkar', 46)
    ,(54, 'Adaxxxxxx', 'Chixxxxxx', 17)
    ,(55, 'Adixxxxxx', 'Ozaxxxxxx', 32)
    ,(56, 'Aisxxxxxx', 'Pagxxxxxx', 48)
    ,(57, 'Aisxxxxxx', 'Welxxxxxx', 48)
    ,(58, 'Amoxxxxxx', 'Khuxxxxxx', 6)
    ,(59, 'Ankxxxxxxr', 'Sinxxxxxx', 26)
    ,(60, 'Anuxxxxxx', 'Jhaxxxxxx', 8)
    ,(61, 'Ashxxxxxx', 'Shaxxxxxx', 8)
    ,(62, 'Asixxxxxx', 'Shaxxxxxx', 26)
    ,(63, 'Dhaxxxxxxe', 'Dhaxxxxxx', 48)
    ,(64, 'Dhaxxxxxxe', 'Surxxxxxxhi', 6)
    ,(65, 'Dnyxxxxxxar', 'Rauxxxxxx', 26)
    ,(66, 'Heexxxxxx', 'Tewxxxxxx', 6)
    ,(67, 'Hitxxxxxx', 'Baixxxxxx', 46)
    ,(68, 'Kanxxxxxx', 'Adhxxxxxx', 25)
    ,(69, 'Kanxxxxxx', 'Ujjxxxxxx', 19)
    ,(70, 'Kapxxxxxx', 'Desxxxxxx', 32)
    ,(71, 'Mahxxxxxx', 'Shexxxxxx', 26)
    ,(72, 'Kunxxxxxx', 'Nikxxxxxx', 15)
    ,(73, 'Nahxxxxxx', 'Shaxxxxxx', 6)
    ,(74, 'Namxxxxxx', 'Dxxxxxx', 32)
    ,(75, 'Nikxxxxxx', 'Patxxxxxx', 32)
    ,(76, 'Nivxxxxxx', 'Jagxxxxxx', 8)
    ,(77, 'Monxxxxxx', 'Kawxxxxxx', 15)
    ,(78, 'Praxxxxxx', 'Gunxxxxxx', 32)
    ,(79, 'Prixxxxxx', 'Visxxxxxxma', 36)
    ,(80, 'Punxxxxxx', 'Domxxxxxx', 36)
    ,(81, 'Rahxxxxxx', 'Chaxxxxxx', 26)
    ,(82, 'Razxxxxxx', 'Shaxxxxxx', 6)
    ,(83, 'Rohxxxxxx', 'Borxxxxxx', 8)
    ,(84, 'Rohxxxxxx', 'Surxxxxxxhi', 6)
    ,(85, 'Rutxxxxxx', 'Wagxxxxxx', 32)
    ,(86, 'Sagxxxxxx', 'Nikxxxxxx', 48)
    ,(87, 'Sagxxxxxx', 'Vadxxxxxx', 32)
    ,(88, 'Salxxxxxx', 'Shaxxxxxx', 32)
    ,(89, 'Saqxxxxxx', 'Desxxxxxx', 8)
    ,(90, 'Sakxxxxxx', 'Visxxxxxx', 46)
    ,(91, 'Sayxxxxxx', 'Zagxxxxxx', 25)
    ,(92, 'Shixxxxxx', 'Kasxxxxxx', 46)
    ,(93, 'Shrxxxxxx', 'Shaxxxxxx', 8)
    ,(94, 'Shrxxxxxx', 'Dasxxxxxx', 15)
    ,(95, 'Snexxxxxx', 'Rahxxxxxx', 17)
    ,(96, 'Swaxxxxxx', 'Jadxxxxxx', 32)
    ,(97, 'Tejxxxxxx', 'Patxxxxxx', 48)
    ,(98, 'Varxxxxxx', 'Kalxxxxxx', 32)
    ,(99, 'Yogxxxxxx', 'Lolxxxxxx', 8)
    ,(100, 'Lukxxxxxx', 'Alixxxxxx', 32)
    ,(101, 'Rajxxxxxx', 'Dhoxxxxxx', 48)
    ,(102, 'Abdxxxxxx', 'Surxxxxxx', 6)
    ,(103, 'Ekrxxxxxx', 'Yozxxxxxx', 25)
    ,(104, 'Camxxxxxx', 'Henxxxxxx', 32)
    ,(105, 'Carxxxxxx', 'Symxxxxxx', 25)
    ,(106, 'Danxxxxxx', 'Huxxxxxxx', 25)
    ,(107, 'Funxxxxxx', 'Adexxxxxx', 25)
    ,(108, 'Iaixxxxxx', 'Horxxxxxx', 32)
    ,(109, 'Ifoxxxxxx', 'Gauxxxxxx', 6)
    ,(110, 'Manxxxxxx', 'Sinxxxxxx', 17)
    ,(111, 'Oluxxxxxxn', 'Adexxxxxx', 15)
    ,(112, 'Satxxxxxx', 'Busxxxxxx', 110)
    ,(113, 'Snexxxxxx', 'Kesxxxxxxy', 15)
    ,(114, 'Venxxxxxx', 'Redxxxxxx', 15)
    ,(115, 'Devxxxxxx', 'Valxxxxxx', 32)
    ,(116, 'Harxxxxxx', 'Devxxxxxx', 6)
    ,(117, 'Kunxxxxxx', 'Patxxxxxx', 48)
    ,(118, 'Pooxxxxxx', 'Kalxxxxxx', 19)
    ,(119, 'Praxxxxxx', 'Dxxxxxx', 17)
    ,(120, 'Rakxxxxxx', 'Sanxxxxxx', NULL)
    ,(121, 'Rohxxxxxx', 'Salxxxxxx', 48)
    ,(122, 'Sanxxxxxx', 'Sonxxxxxx', 8)
    ,(123, 'Sarxxxxxx', 'Sonxxxxxx', 17)
    ,(124, 'Sumxxxxxx', 'Hanxxxxxx', 6)
    ,(125, 'Tejxxxxxx', 'Bhaxxxxxx', 32)
    ,(126, 'Utkxxxxxx', 'Purxxxxxx', 26)
    ,(127, 'Olexxxxxx', 'Podxxxxxx', 6)
    ,(128, 'Visxxxxxx', 'Patxxxxxx', 8)
    ,(129, 'Amoxxxxxx', 'Pawxxxxxx', 26)
    ,(130, 'Tomxxxxxx', 'Edwxxxxxx', 46)
    ,(131, 'Attxxxxxx', 'Sagxxxxxx', 9)
    ,(132, 'Berxxxxxx', 'Gomxxxxxx', 24)
    ,(133, 'Shixxxxxx', 'Chaxxxxxx', 19)
    ,(202, 'Ashxxxxxx', 'Somxxxxxx', 2)
    ,(203, 'Catxxxxxx', 'Sanxxxxxx', 2)
    ,(204, 'Ginxxxxxx', 'Thoxxxxxx', 133)
    ,(205, 'Lukxxxxxx', 'Endxxxxxx', 50)
    ,(206, 'Praxxxxxx', 'Desxxxxxx', 24)
    ,(207, 'Saixxxxxx', 'Shaxxxxxx', 9)
    ,(208, 'Timxxxxxx', 'Stoxxxxxx', 19)
    ,(209, 'Matxxxxxx', 'Wilxxxxxx', NULL)
    ,(210, 'Nigxxxxxx', 'Strxxxxxx-Wa', NULL)
    ,(211, 'Sarxxxxxx', 'Lomxxxxxx', 210)
    ,(212, 'Stexxxxxx', 'Broxxxxxx', 210)
    ,(213, 'Antxxxxxx', 'Tylxxxxxx', 210)
    ,(214, 'Chrxxxxxxer', 'Tufxxxxxx', 211)
    ,(215, 'Phoxxxxxx', 'Mofxxxxxx', 213)
    ,(216, 'Sarxxxxxx', 'Claxxxxxx', 212)
    ,(217, 'Stuxxxxxx', 'Wesxxxxxx', 216)
    ,(218, 'Chrxxxxxxer', 'Kinxxxxxx', 216)
    ,(219, 'Lauxxxxxx', 'Henxxxxxx', 216)
    ,(220, 'Jorxxxxxx', 'Tayxxxxxx', 212)
    ,(221, 'Johxxxxxx', 'Jesxxxxxx', 220)
    ,(222, 'Ibrxxxxxx', 'Ougxxxxxx', 214)
    ,(223, 'Sarxxxxxx', 'Rodxxxxxx', 211)
    ,(224, 'Rebxxxxxx', 'Sauxxxxxx', 211)
    ,(225, 'Anyxxxxxx', 'Wraxxxxxx', 211)
    ,(226, 'Danxxxxxx', 'Ciuxxxxxx', 225)
    ,(227, 'Nicxxxxxx', 'Grexxxxxx', 225)
    ,(228, 'Antxxxxxx', 'Wadxxxxxx', 225)
    ,(229, 'Benxxxxxx', 'Twixxxxxx', 226)
    ,(230, 'Hanxxxxxx', 'Arhxxxxxx', 226)
    ,(231, 'Samxxxxxx', 'Watxxxxxx', 226)
    ,(232, 'Naoxxxxxx', 'Jonxxxxxx', 225)
    ,(233, 'Neexxxxxx', 'Gauxxxxxx', 227)
    ,(234, 'Racxxxxxx', 'Danxxxxxx', 232)
    ,(235, 'Johxxxxxx', 'Pitxxxxxx', 228)
    ,(236, 'Lucxxxxxx', 'Cogxxxxxx', 228)
    ,(237, 'Hetxxxxxx', 'Ivexxxxxx', 228)
    ,(238, 'Carxxxxxx', 'Powxxxxxx', 212)
    ,(239, 'Sarxxxxxx', 'Evaxxxxxx', 212)
    ,(240, 'Adrxxxxxx', 'Ardxxxxxx', 226)
    ,(241, 'Chixxxxxx', 'Igwxxxxxx', 226)
    ,(242, 'Camxxxxxx', 'Swaxxxxxx', 226)
    ,(243, 'Vicxxxxxx', 'Milxxxxxx', 209)
    ,(244, 'Suexxxxxx', 'Dauxxxxxx', 243)
    ,(245, 'Sarxxxxxx', 'Kemxxxxxx', 243)
    ,(246, 'Gayxxxxxx', 'Madxxxxxx', 243)
    ,(247, 'Wilxxxxxx', 'Vogxxxxxx', 24)
    ,(248, 'Albxxxxxx', 'FerxxxxxxVad', 6);

    SELECT *
    FROM #Person p;

  • That's me told. My 6,000 points arise mainly from answering QotD, but apologies for supplying everything in the wrong order and in the wrong format.  I'll try to do better next time.

    • This reply was modified 1 week, 5 days ago by  edwardwill.
  • Also, I've noticed that there's an error in the data, rectified by this script:

    UPDATE

    [People].[Person]

    SET

    LineManagerId =209

    WHERE

    (Id = 19);

  • edwardwill wrote:

    That's me told. My 6,000 points arise mainly from answering QotD, but apologies for supplying everything in the wrong order and in the wrong format.  I'll try to do better next time.

    Nice one. There are a few repeat offenders on this forum (not you) regarding the provision of consumable data & it winds me up a bit. Not your fault.


  • There are examples on the internet of how to recursively assign HIERARCHYID.  A while ago on this forum:

    Using hierarchyID

    My reply in the SSC forum was based on this Stack Overflow answer

    Full recursive employee-boss relation in SQL Server

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thanks to all.  I adapted Steve's code and it seems to work correctly.  Now all I have to do is figure out how to use it in C#/LINQ!

  • edwardwill wrote:

    Thanks to all.  I adapted Steve's code and it seems to work correctly.  Now all I have to do is figure out how to use it in C#/LINQ!

    Simple answer: put the logic into T-SQL stored procedures and call those from C#.


  • Phil Parkin wrote:

    edwardwill wrote:

    Thanks to all.  I adapted Steve's code and it seems to work correctly.  Now all I have to do is figure out how to use it in C#/LINQ!

    Simple answer: put the logic into T-SQL stored procedures and call those from C#.

    Unfortunately that's not the accepted pattern.  We only use stored procedures in very prescribed circumstances.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply