List all the ancestors or parent nodes of a particular node present at each level in pivoted table containing levels as attributes in SQL Server

Question

I have a table 'temp' which has id and its immediate parent's id as columns. The table is as follows:

                              1
                            /   \
                           2     3
                          /|\     \
                         4 5 6     7
                        /
                       8

Hierarchy of nodes can be represented in tree structure as above.

Now, I want to list all the ancestor or parent nodes of each node present at all levels in pivoted table using recursive cte which has levels (like Level1, Level2 and so on) as its attributes. To get this output, I have calculated all the parent nodes in non pivoted table with level of each node with respect to its parent. The sql query for which is below:

WITH ctetable as 
(
    SELECT S.id, S.parent, 1 as level
    FROM temp as S where S.parent is not null
    UNION ALL
    SELECT S2.id, p.parent, p.level + 1
    FROM ctetable AS p JOIN temp as S2 on S2.parent = p.id
)
SELECT * FROM ctetable ORDER BY id;

The output of above query is shown below:

But, I want to pivot the recursive cte which contains the parent id under at each level of a particular node. Say, for id=4, it should display parent id 4, 2 and 1 under Level3, Level2 and Level1 respectively. For this I wrote the following query:

WITH ctetable as 
(
    SELECT S.id, S.parent, 1 as level
    FROM temp as S where S.parent is not null
    UNION ALL
    SELECT S2.id, p.parent, p.level + 1
    FROM ctetable AS p JOIN temp as S2 on S2.parent = p.id
)

SELECT      
            myid,
            [pn].[1] AS [Level1],
            [pn].[2] AS [Level2],
            [pn].[3] AS [Level3] 
FROM
     (
         SELECT [a].id,
                [a].id as myid,
                [a].level
         FROM ctetable AS [a]
     ) AS [hn] PIVOT(max([hn].id) FOR [hn].level IN([1],[2],[3])) AS [pn]

But, the output table is not the desired one as it contains the same id repeated as parent id under each level for a particular node instead it should contain all the parents of that node under various levels. The output i got after executing the above query is shown below:

Can anybody help me out with this....

Answer 1

If you have a known or maximum number of levels, and assuming I did not reverse your desired results.

Also Best if you post sample data and desired results as text, not as an image

Example

Declare @YourTable Table ([id] int,[parent] int)
Insert Into @YourTable Values 
 (1,null)
,(2,1)
,(3,1)
,(7,3)
,(4,2)
,(5,2)
,(6,2)
,(8,4)

;with cteP as (
      Select id
            ,Parent 
            ,PathID = cast(10000+id as varchar(500))
      From   @YourTable
      Where  Parent is Null
      Union  All
      Select id  = r.id
            ,Parent  = r.Parent 
            ,PathID = cast(concat(p.PathID,',',10000+r.id) as varchar(500))
      From   @YourTable r
      Join   cteP p on r.Parent  = p.id)
Select ID
      ,B.*
 From  cteP A
 Cross Apply (
                Select Level1 = xDim.value('/x[1]','int')-10000
                      ,Level2 = xDim.value('/x[2]','int')-10000
                      ,Level3 = xDim.value('/x[3]','int')-10000
                      ,Level4 = xDim.value('/x[4]','int')-10000
                      ,Level5 = xDim.value('/x[5]','int')-10000
                From  (Select Cast('<x>' + replace(PathID,',','</x><x>')+'</x>' as xml) as xDim) as X 
             ) B
  Order By PathID

Returns

EDIT - Added +10000

I added the +10000 so that the sequence will be maintained