Menu
I am trying to perform what I believe is a difficult recursion using a CTE is SQL Server 2008. I can't seem to wrap my head around this one.
In the below examples you can assume a fixed depth of 3...nothing will ever be lower than that. In real life, the depth is "deeper" but still fixed. In the example I tried to simplify it some.
My input data is like the below.
ID PARENT_ID NAME DEPTH
------------------------------------------
1 NULL A 1
2 1 B 2
3 2 C 3
4 1 D 2
The output of my CTE should be the following table.
LEVEL1_ID LEVEL2_ID LEVEL3_ID LEVEL1_NAME LEVEL2_NAME LEVEL3_NAME
--------------------------------------------------------------------------------
1 NULL NULL A NULL NULL
1 2 NULL A B NULL
1 2 3 A B C
1 4 NULL A D NULL
If I can get the ID columns in the output I can certainly map to names in a lookup table.
I am open to other ways of accomplishing this as well, including using SSIS.
525
A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. FROM cte_name; Again, at the beginning of your CTE is the WITH clause.
A Recursive CTE is a CTE that references itself. The CTE repeatedly executes, returns subsets of data, until it returns the complete result set. Syntax. WITH cte_name AS ( cte_query_definition (or) initial query -- Anchor member UNION ALL recursive_query with condition -- Recursive member ) SELECT * FROM cte_name.
Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later. Naming the result and referencing it within other queries.
CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.
Not really all that hard to do:
;WITH cte AS
(
SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
FROM dbo.YourTable
WHERE parent_id IS NULL
UNION ALL
SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
FROM dbo.YourTable t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY ID
Gives me an output of:
/A
/A/B
/A/B/C
/A/D
As a side-note: the "depth" could be easily computed by the CTE and you don't necessarily need to store that in your table (see the Level column I've added):
;WITH cte AS
(
SELECT
CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID,
1 AS 'Level'
FROM dbo.YourTable
WHERE parent_id IS NULL
UNION ALL
SELECT
CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID,
cte.Level + 1 AS 'Level'
FROM dbo.YourTable t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY Level, ID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
