May 9, 2025 at 9:23 am
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?
May 9, 2025 at 9:56 am
Nice question, but how about also giving us some consumable sample data to test with?
May 9, 2025 at 11:35 am
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;
May 9, 2025 at 11:43 am
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.
May 9, 2025 at 12:10 pm
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);
May 9, 2025 at 1:05 pm
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.
May 10, 2025 at 3:58 pm
There are examples on the internet of how to recursively assign HIERARCHYID. A while ago on this forum:
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
May 12, 2025 at 7:30 am
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!
May 12, 2025 at 8:14 am
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