CTE SQL Server 2008 Recursion

Question

I am trying to accomplish what, in my opinion, is complex recursion using CTE, is SQL Server 2008. I cannot wrap my head around this.

In the examples below, you can take a fixed depth of 3 ... nothing will ever be lower than that. In real life, the depth is "deeper", but still fixed. In this example, I tried to simplify it.

My input is similar to 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 AB NULL 1 2 3 ABC 1 4 NULL AD NULL 

If I can get the identifier columns as a result of the output, I can of course match the names in the lookup table.

I am open to other ways to solve this problem, including using SSIS.

Answer 1

Not everything is so difficult 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 the conclusion:

 /A /A/B /A/B/C /A/D 

As a side note: the “depth” can be easily calculated by the CTE, and you don't have to save it in your table (see the Level column that I 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 

Answer 2

I don’t remember, you can make a subquery in cte.

I don't have a copy of the sql server here, but you can try using this code:

 WITH cte(id, path, level) AS ( SELECT id, '/' + name, level FROM yourtable WHERE level = 1 UNION ALL SELECT y.id, c.name + '/' + y.name, y.level FROM yourtable y INNER JOIN cte c ON c.id = y.parent_id WHERE level = (SELECT max(level)+1 from cte) ) SELECT path from cte 

Answer 3

 ;WITH Vals AS ( SELECT CASE DEPTH WHEN 1 THEN ID ELSE NULL END 'LEVEL1_ID ', CASE DEPTH WHEN 2 THEN ID ELSE NULL END 'LEVEL2_ID ', CASE DEPTH WHEN 3 THEN ID ELSE NULL END 'LEVEL3_ID ', CASE DEPTH WHEN 1 THEN NAME ELSE NULL END 'LEVEL1_NAME', CASE DEPTH WHEN 2 THEN NAME ELSE NULL END 'LEVEL2_NAME', CASE DEPTH WHEN 3 THEN NAME ELSE NULL END 'LEVEL3_NAME', ID 'PRMID' FROM #Table1 WHERE parentId IS NULL UNION ALL SELECT CASE DEPTH WHEN 1 THEN ID ELSE LEVEL1_ID END 'LEVEL1_ID ', CASE DEPTH WHEN 2 THEN ID ELSE LEVEL2_ID END 'LEVEL2_ID ', CASE DEPTH WHEN 3 THEN ID ELSE LEVEL3_ID END 'LEVEL3_ID ', CASE DEPTH WHEN 1 THEN NAME ELSE LEVEL1_NAME END 'LEVEL1_NAME', CASE DEPTH WHEN 2 THEN NAME ELSE LEVEL2_NAME END 'LEVEL2_NAME', CASE DEPTH WHEN 3 THEN NAME ELSE LEVEL3_NAME END 'LEVEL3_NAME', ID 'PRMID' FROM #Table1 inner join Vals on #Table1.parentId=PRMID ) SELECT * from Vals