Выполните обновление значения и используйте это значение для обновления другого значения во время того же процесса обновления

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 
    *
FROM 
    ##People1
ORDER BY 
    RowID;
GO

Вы можете запустить sql здесь: SqlOnline

ИЛИ

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,
    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


DROP TABLE IF EXISTS #Hierarchy;

CREATE TABLE #Hierarchy
(
    RowID INT PRIMARY KEY,
    PersonID INT,
    PersonName VARCHAR(100),
    ReportsTo INT,
    SeeMyData VARCHAR(MAX),
    Path VARCHAR(MAX)
);


INSERT INTO #Hierarchy (RowID, PersonID, PersonName, ReportsTo, SeeMyData, Path)
SELECT 
    RowID,
    PersonID,
    PersonName,
    ReportsTo,
    NULL AS SeeMyData,
    CAST(PersonID AS VARCHAR(MAX)) AS Path
FROM 
    ##People1
WHERE 
    ReportsTo IS NULL;

-- Iteratively insert people who report to those already in the temporary table
DECLARE @RowCount INT = 1;

WHILE @RowCount > 0
BEGIN
    INSERT INTO #Hierarchy (RowID, PersonID, PersonName, ReportsTo, SeeMyData, Path)
    SELECT 
        p.RowID,
        p.PersonID,
        p.PersonName,
        p.ReportsTo,
        NULL AS SeeMyData,
        CAST(h.Path + '-' + CAST(p.PersonID AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Path
    FROM 
        ##People1 p
    INNER JOIN 
        #Hierarchy h ON p.ReportsTo = h.PersonID
    WHERE 
        NOT EXISTS (SELECT 1 FROM #Hierarchy WHERE RowID = p.RowID);

    SET @RowCount = @@ROWCOUNT;
END;

-- Update SeeMyData column
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 #Hierarchy h ON p.RowID = h.RowID;

--output
SELECT 
    *
FROM 
    ##People1
ORDER BY 
    RowID;
GO