# Creating a recursive CTE
**URL:** https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380
**Category:** Matillion ETL
**Created:** [June 28, 2021, 9:05pm UTC](https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380 "2021-06-28T21:05:30Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@mkeiper](https://matillioncommunity.discourse.group/u/mkeiper)
#### Post date: [June 28, 2021, 9:05pm UTC](https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380/1 "2021-06-28T21:05:30Z")
I have some Employee Data in which I am creating wanting to create a Hierarchy for. Each employee record has their direct report listed. I would like to be able to rank Employees from top to bottom based on how many superiors are ahead of them.
I have figured out how to do this in the SQL Component but wondering if there is a way to do this through a Matillion ETL workflow
---
### Author:  [@ian.funnell](https://matillioncommunity.discourse.group/u/ian.funnell)
#### Post date: [June 29, 2021, 6:18am UTC](https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380/2 "2021-06-29T06:18:42Z")
Hi MK,
The best way currently is (as you have already found) using a [SQL Component](https://documentation.matillion.com/docs/1991934). It allows you to write your own complete SQL SELECT statement inside a Transformation Job.
We have not yet added a new transformation feature for recursive CTEs. Partly because they are still a relatively new capability for CDWs, and partly because we are not sure how often it would be useful.
I am keen to hear more potential use cases for recursive CTEs. Please reply on this thread if you are interested in developments in that area.
Best regards,
Ian
---
### Author:  [@mkeiper](https://matillioncommunity.discourse.group/u/mkeiper)
#### Post date: [June 29, 2021, 6:36pm UTC](https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380/3 "2021-06-29T18:36:31Z")
I am interested. Thanks for your response Ian!
---
### Author:  [@caleb3611](https://matillioncommunity.discourse.group/u/caleb3611)
#### Post date: [August 2, 2022, 9:37pm UTC](https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380/4 "2022-08-02T21:37:55Z")
Curious if any work has been done to make recursive ctes compatible with transformations. I'm trying to get a job set up to populate a table and the query fueling it relies on a recursive cte
---
### Author:  [@JStooke](https://matillioncommunity.discourse.group/u/JStooke)
#### Post date: [October 24, 2022, 9:22am UTC](https://matillioncommunity.discourse.group/t/creating-a-recursive-cte/1380/5 "2022-10-24T09:22:24Z")
Still requires the SQL Component but had a lot of success using connect by clause instead of recursive CTE for hierarchical queries: [https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html](https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html)