DROP TABLE IF EXISTS ##People1;
CREATE TABLE ##People1
(
RowID INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
PersonID INT NULL,
PersonName VARCHAR(100) NULL,
ReportsTo INT NULL, -- Changed to INT to match PersonID type
SeeMyData VARCHAR(MAX) NULL
);
GO
INSERT INTO ##People1
(PersonID, PersonName, ReportsTo)
VALUES
(7036, 'Liesl',NULL),
(4049, 'Friedrich',7036),
(197,'Louisa',4049),
(2303,'Kurt',197),
(3409,'Brigitta',2303),
(5686,'Marta',4049),
(533,'Gretl',5686),
(5204,'Mike',533),
(4063,'Sara',3409),
(1928,'Tom',197),
(7013,'Jerry',1928),
(7033,'Sue',533);
GO
WITH HierarchyCTE AS
(
SELECT
RowID,
PersonID,
PersonName,
ReportsTo,
CAST(NULL AS VARCHAR(MAX)) AS SeeMyData,
CAST(CAST(PersonID AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Path
FROM
##People1
WHERE
ReportsTo IS NULL
UNION ALL
SELECT
p.RowID,
p.PersonID,
p.PersonName,
p.ReportsTo,
CAST(NULL AS VARCHAR(MAX)) AS SeeMyData,
CAST(h.Path + '-' + CAST(p.PersonID AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Path
FROM
##People1 p
INNER JOIN
HierarchyCTE h ON p.ReportsTo = h.PersonID
)
UPDATE p
SET SeeMyData = CASE WHEN p.ReportsTo IS NULL THEN NULL ELSE SUBSTRING(h.Path, 1, LEN(h.Path) - LEN(CAST(p.PersonID AS VARCHAR(MAX))) - 1) END
FROM ##People1 p
INNER JOIN HierarchyCTE h ON p.RowID = h.RowID;
-- Select the final output
SELECT
RowID,
PersonID,
PersonName,
ReportsTo,
SeeMyData
FROM
##People1
ORDER BY
RowID;
GO
Telegram: DB developers channel