How implementing an hierarchy without using a recursive CTE

[pmscorca]

Hi,

I've noticed that the warehouse T-SQL doesn't support the recursive ctes and this is a pity!

Now, how could I implement an hierarchy in T-SQL without a such useful and comfortable feature, please? Thanks

In the future, will fabric support this feature?

[JanisAlksnisRS]

Hi!

In case you have limited number of hierarchy levels and have some nullable kind of parent field, you can use the approach I used for "flattening" list of the organizational units:

SELECT
org_units.id_org_unit
, org_units.id_parent
, org_units.org_unit_code
, org_units.org_unit
, org_unit_hierarchy.top_unit_code
, org_unit_hierarchy.top_unit
, org_unit_hierarchy.middle_unit_code
, org_unit_hierarchy.middle_unit
, org_unit_hierarchy.low_unit_code
, org_unit_hierarchy.low_unit
FROM org_units
INNER JOIN (
SELECT
base_unit.id_org_unit
, CASE
WHEN third_unit.org_unit_code IS NOT NULL THEN third_unit.org_unit_code
WHEN second_unit.org_unit_code IS NOT NULL THEN second_unit.org_unit_code
ELSE base_unit.org_unit_code
END AS top_unit_code
, CASE
WHEN third_unit.org_unit IS NOT NULL THEN third_unit.org_unit
WHEN second_unit.org_unit IS NOT NULL THEN second_unit.org_unit
ELSE base_unit.org_unit
END AS top_unit
, CASE
WHEN third_unit.org_unit_code IS NOT NULL THEN second_unit.org_unit_code
WHEN second_unit.org_unit_code IS NOT NULL THEN base_unit.org_unit_code
END AS middle_unit_code
, CASE
WHEN third_unit.org_unit IS NOT NULL THEN second_unit.org_unit
WHEN second_unit.org_unit IS NOT NULL THEN base_unit.org_unit
END AS middle_unit
, CASE
WHEN third_unit.org_unit_code IS NOT NULL THEN base_unit.org_unit_code
END AS low_unit_code
, CASE
WHEN third_unit.org_unit IS NOT NULL THEN base_unit.org_unit
END AS low_unit
FROM org_units AS base_unit
LEFT OUTER JOIN org_units AS second_unit ON base_unit.id_parent = second_unit.id_org_unit
LEFT OUTER JOIN org_units AS third_unit ON second_unit.id_parent = third_unit.id_org_unit
) AS org_unit_hierarchy

[rubayatyasmin]

Hi @pmscorca 

Did you try the closure table? sql - How can I create a closure table using data from an adjacency list? - Stack Overflow

[lbendlin]

Does it have to be in T-SQL?  Could you use the DAX PATH functions?

[pmscorca]

Hi, I need to have an hierarchy at warehouse level and not at semantic model level.

However, the recursive CTEs are also useful for other purposes.

Update: I've created this idea Adding support for recursive CTEs in a warehouse ; please vote it, thanks.

[v-menakakota]

 Hi @pmscorca ,

Thanks for reaching out to the Microsoft fabric community forum.

You’ve taken the right step by raising and sharing the idea to add recursive CTE support. That’s currently the best path forward, as this is a platform capability gap rather than a configuration issue. Encouraging others to vote on it will help increase visibility and priority.

Best Regards, 
Community Support Team