检查以下脚本-

WITH CTE(RN,c_id,p_id)
AS
(
    SELECT 1,1, NULL UNION ALL
    SELECT 1,2, 1 UNION ALL
    SELECT 1,3, 1 UNION ALL
    SELECT 1,4, 2 UNION ALL
    SELECT 1,5, 4 UNION ALL
    SELECT 1,6, 5 UNION ALL
    SELECT 1,7, 6

    --Your query will be for CTE as below
    --SELECT 1 RN,c_id,p_id FROM your_table
)

SELECT c_id,
MAX (CASE WHEN RN = 2 THEN p_id ELSE NULL END) L1Parent,
MAX (CASE WHEN RN = 3 THEN p_id ELSE NULL END) L2Parent,
MAX (CASE WHEN RN = 4 THEN p_id ELSE NULL END) L3Parent,
MAX (CASE WHEN RN = 5 THEN p_id ELSE NULL END) L4Parent,
MAX (CASE WHEN RN = 6 THEN p_id ELSE NULL END) L5Parent
FROM
(
        SELECT *,ROW_NUMBER() OVER(PARTITION BY B.c_id ORDER BY B.c_id,B.p_id) RN 
        FROM
        (
            SELECT DISTINCT A.c_id,B.p_id           
            FROM CTE A
            INNER JOIN CTE B
            ON A.RN = B.RN AND A.c_id >= B.c_id
        )B
)A
GROUP BY c_id

输出为-

c_id    L1Parent    L2Parent    L3Parent    L4Parent    L5Parent
1       NULL        NULL        NULL        NULL        NULL
2       1           NULL        NULL        NULL        NULL
3       1           NULL        NULL        NULL        NULL
4       1           2           NULL        NULL        NULL
5       1           2           4           NULL        NULL
6       1           2           4           5           NULL
7       1           2           4           5           6