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.
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.