Make parent and child hierarchy with records for parents as well as children

I have a parent-child id_table hierarchy – e.g.

|parent|child|
|------|-----|
|      |    0|
|     0|    1|
|     0|    2|
|     0|    3|
|     1|    4|
|     1|    5|
|     2|    6|
|     4|    7|
|     4|    8|

I’m building a visual tree hierarchy, where above data would be formatted as:

|parent|child1|child2|child3
|------|------|------|------
|     0|     1|4     |     7
|     0|     1|4     |     8
|     0|     1|5     |
|     0|     2|6     |
|     0|     3|      |

Now I want to modify this query to include a row for each parent standalone without the child, so above data would become:

|parent|child1|child2|child3
|------|------|------|------
|     0|      |      |
|     0|     1|      |
|     0|     1|     4|
|     0|     1|     4|     7
|     0|     1|     4|     8
|     0|     1|     5|
|     0|     2|      |
|     0|     2|     6|
|     0|     3|      |

To get the first result, I am building the data with repeated left joins (using above first data example) as to my understanding I can’t do this with recursion, e.g.:

SELECT t1.child AS parent
       t2.child AS child1
       t3.child AS child2
       t4.child AS child3
FROM id_table t1

LEFT JOIN id_table t2
ON t1.child = t2.parent

LEFT JOIN id_table t3
ON t1.child = t3.parent

LEFT JOIN id_table t4
ON t1.child = t4.parent

WHERE t1.child = '0'

This gets me the second example, but I’m lacking a record for each parent as well, as shown in the third example.

I assume this is probably a simple question, I’m just struggling with the syntax. TIA for any help.

EDIT: I had a prior question for a similar implementation in SAS EG: SQL – Recursive Tree Hierarchy with Record at Each Level, however that was with the SAS SQL implementation which is much more restricted – with that method I eventually had to just create temp tables at each level then union the end result, which was messy. Trying to find a cleaner solution.

Answer

GROUP BY ROLLUP can be used to create those extra rows:

SELECT DISTINCT
   t1.child AS Parent
  ,t2.child AS child1
  ,t3.child AS child2
  ,t4.child AS child3
  -- one more column for each additional level
FROM id_table t1

LEFT JOIN id_table t2
ON t1.child = t2.Parent

LEFT JOIN id_table t3
ON t2.child = t3.Parent

LEFT JOIN id_table t4
ON t3.child = t4.Parent

-- one additional join for each new level
WHERE t1.child = '0'

GROUP BY ROLLUP (t1.child,t2.child,t3.child,t4.child) 
HAVING t1.child IS NOT NULL

Or a Recursive Query to traverse through the hierarchy, built the path and then split it into columns:

WITH RECURSIVE cte AS
 ( -- traverse the hierarchy and built the path
   SELECT 1 AS lvl,
     ,child
     ,Cast(child AS VARCHAR(500)) AS Path -- must be large enough for concatenating all levels
   FROM id_table
   WHERE Parent IS NULL

   UNION ALL

   SELECT lvl+1
     ,t.child
     ,cte.Path || ',' || Trim(t.child)   
   FROM cte JOIN id_table AS t
   ON cte.child = t.Parent
   WHERE lvl < 20 -- just in case there's an endless loop
 ) 
SELECT 
   StrTok(Path, ',', 1) 
  ,StrTok(Path, ',', 2) 
  ,StrTok(Path, ',', 3) 
  ,StrTok(Path, ',', 4) 
  -- one additional StrTok for each new level
FROM cte

Don’t know which one is more efficient.