How to flatten parent-child hierarchy from bottom up using CTE

for my first question on stack overflow I have the following problem, I’ve got an hierarchy table that looks as follows:

Table TEST

 - [wbe_node_type] [varchar](50) NOT NULL,  /* leaf or expanded */
 - [wbe_node] [varchar](50) NOT NULL,
 - [wbe_node_mask] [varchar](50) NOT NULL,
 - [wbe_description] [varchar](50) NOT NULL,
 - [wbe_parent] [varchar](50) NOT NULL,    
 - [wbe_node_niveau] [int] NOT NULL,        /* level of the record in the hierarchy */

The data looks as follows:

wbe_node_type, wbe_node, wbe_node_mask, wbe_omschrijving, wbe_parent, wbe_node_niveau

expanded, AFCARN0, A-FCARN0, Project 1, [NULL]. 0
leaf, AFCARN04010, A-FCARN0.40.10, Description 2, AFCARN040, 2
expanded, AFCARN040, A-FCARN0.40, Realisatie, AFCARN0, 1

the row with the wbe_node_type ‘leaf’ is the lowest level in the hierarchy, maximum depth is 8 levels deep. What I want is to flatten the hierarchy in one row from the bottom level (the ‘leaf’ node) to the top level.

My thought was to start to find al records with wbe_node_type = ‘leaf’ and find all the levels above it. This is easy to do with a temp-table. However, the tool I’m using to query this data doesn’t work with temp-tables. So I wanted to try it using a CTE.

When I’m looking for examples online, these always start at the top and work their way down. Is it possible to populate a CTE from the bottom starting with the lowest levels?

Answer

Not 100% sure on this but perhaps something like this.

  SELECT 
*
 FROM TEST l3 
 Inner JOIN TEST l2 ON l3.wbe_parent = l2.wbe_node
 Inner JOIN TEST l1 ON l2.wbe_parent = l1.wbe_node
 WHERE 
l3.wbe_node = 'AFCARN04010'

Seems to work