List all parents/ascendants of all nodes I have the following structure:
Example Tree:

Example Table:
    [table]

    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   2
    5   2
    6   2
    7   3
    8   4

For each id, I want to list all of its parents, including its ascendants in a separate row.
Desired Output:
    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   1
    4   2
    5   1
    5   2
    6   1
    6   2
    7   1
    7   3
    8   1
    8   2
    8   4

I tried to use a CTE, but I can't seem to wrap my head around it.
    with temp(id,parent) as (
    SELECT S.id, S.parent
    FROM [table] as S 

    UNION ALL
    
    SELECT S2.id, S2.parent
    FROM [table] as S2 
    inner join temp on S2.id=temp.parent and temp.id is not null
    )
    
    SELECT * FROM temp order by id

I'm trying to traverse up a hierarchy tree and list all the nodes it hits in a separate line for all starting points of the tree. This is how the sample data was given to me.
You were on the right path and almost had it.  In the select of the second section the parent column should come from the cte instead of S2 and also in the second section the join was backwards (S2.id = p.parent vs S2.parent = p.id).  But that's it!

create table [table]
(
id int,
parent int
);

insert into [table] values(1,NULL),
    (2,1),
    (3,1),
    (4,2),
    (5,2),
    (6,2),
    (7,3),
    (8,4);


;WITH ctetable(id, parent, depth, path) as 
(
    SELECT S.id, S.parent, 1 AS depth, convert(varchar(100), S.id) AS path
    FROM [table] as S
    UNION ALL
    SELECT S2.id, p.parent, p.depth + 1 AS depth, convert(varchar(100), (RTRIM(p.path) +'-&gt;'+ convert(varchar(100), S2.id)))
    FROM ctetable AS p JOIN [table] as S2 on S2.parent = p.id
    WHERE p.parent is not null
)
SELECT * FROM ctetable ORDER BY id, parent;