topic Re: How implementing an hierarchy without using a recursive CTE in Data Warehouse

How implementing an hierarchy without using a recursive CTE

pmscorca — Mon, 15 Dec 2025 17:08:44 GMT

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?

Re: How implementing an hierarchy without using a recursive CTE

lbendlin — Mon, 15 Dec 2025 21:14:54 GMT

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

Re: How implementing an hierarchy without using a recursive CTE

pmscorca — Tue, 16 Dec 2025 06:25:25 GMT

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.

Re: How implementing an hierarchy without using a recursive CTE

v-menakakota — Wed, 17 Dec 2025 05:35:03 GMT

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

Re: How implementing an hierarchy without using a recursive CTE

rubayatyasmin — Wed, 24 Dec 2025 23:17:12 GMT

Hi pmscorca

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

Re: How implementing an hierarchy without using a recursive CTE

JanisAlksnisRS — Wed, 07 Jan 2026 12:08:46 GMT

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