SQL Server self recursive horizontal join I need to generate a query which provides hierarchical data in a horizontal way. The levels of depth are variable, it is ok if there are many empty fields. The only rows that should exist should be just children. Here's an example of what I need:  

ID   |Value  |ID_Parent
-----|-------|---------
1    |NULL   |NULL
2    |NULL   |1
3    |30     |1
4    |NULL   |2
5    |20     |4
6    |10     |NULL


So the output to this would be something like:

ID   |Value  |Ancestor_1 |Ancestor_2 |Ancestor_3
-----|-------|-----------|-----------|----------
3    |30     |1          |NULL       |NULL
5    |20     |1          |2          |4
6    |10     |NULL       |NULL       |NULL


It is needed that the ancestors are labeled 1-to-n as farest-to-nearest.
A tree view of the data would be this one:

1-&gt;/2-&gt;4-&gt;5
   \3
6


I've found a solution for fixed levels but I was wondering if this can be done in SQL Server.
I feel there are two approaches - left to right starting anywhere that has no parent, and right to left for anywhere that has a value. I'm going to go with the latter because it matches the rows to be produced.

You should start by using a recursive CTE to turn your data into:

RowVal, NodeVal, Value, Level
3, 3, 30, 0
3, 1, 30, 1
5, 5, 20, 0
5, 4, 20, 1
5, 2, 20, 2
5, 1, 20, 3
6, 6, 10, 0


There are plenty of examples for doing this...

WITH theTree AS (
    SELECT ID AS RowVal, ID AS NodeVal, Value, 0 AS Level, ID_Parent
    FROM theTable 
    WHERE Value IS NOT NULL
    UNION ALL
    SELECT tr.RowVal, ta.ID AS NodeVal, tr.Value, Level + 1 AS Level, ta.ID_Parent
    FROM theTree AS tr
    JOIN theTable AS ta ON ta.ID = tr.ID_Parent
)
SELECT RowVal, NodeVal, Value, Level
FROM theTree
;


Now you need to adjust the Levels, because we started at the leaves.

MAX(Level) OVER (PARTITION BY RowVal) - Level AS NewLevel


Now it's simple dynamic pivot stuff, which you can find very easily. Group by RowVal, Value. A column per NewLevel. And MAX(NodeVal) in each "cell".