SQL - Flattening a tree view

Question

I am using a tree control in a form, which uses columns ID, ParentID, ReasonText. eg:

ID ParentID ReasonText
1  0        Level1A
2  0        Level1B
3  1        Level2AA
4  1        Level2AB
5  2        Level2BA
6  4        Level3ABA

I want to get the structure from SQL, such that if I select ID=6 for instance I want to return "Level1A>Level2AB>Level3ABA" and 1 would return "Level1A". I have a complicated SQL statement which does it up to 4 levels.

DECLARE @ipid int = 45
DECLARE @pathdelimiter varchar = '>'
SELECT
    R1.ID, N'' + 
    CASE WHEN (SELECT R4.ReasonText FROM CLG_CallReasonTree R4 WHERE R4.ID = (SELECT R3.ParentID FROM CLG_CallReasonTree R3 WHERE R3.ID = (SELECT R2.ParentID FROM CLG_CallReasonTree R2 WHERE R2.ID = R1.ParentID))) IS NOT NULL
    THEN (SELECT R4.ReasonText FROM CLG_CallReasonTree R4 WHERE R4.ID = (SELECT R3.ParentID FROM CLG_CallReasonTree R3 WHERE R3.ID = (SELECT R2.ParentID FROM CLG_CallReasonTree R2 WHERE R2.ID = R1.ParentID))) + @pathdelimiter  ELSE '' END +
    CASE WHEN (SELECT R3.ReasonText FROM CLG_CallReasonTree R3 WHERE R3.ID = (SELECT R2.ParentID FROM CLG_CallReasonTree R2 WHERE R2.ID = R1.ParentID)) IS NOT NULL
    THEN (SELECT R3.ReasonText FROM CLG_CallReasonTree R3 WHERE R3.ID = (SELECT R2.ParentID FROM CLG_CallReasonTree R2 WHERE R2.ID = R1.ParentID)) + @pathdelimiter  ELSE '' END +
    CASE WHEN (SELECT R2.ReasonText FROM CLG_CallReasonTree R2 WHERE R2.ID = R1.ParentID) IS NOT NULL
    THEN (SELECT R2.ReasonText FROM CLG_CallReasonTree R2 WHERE R2.ID = R1.ParentID) + @pathdelimiter ELSE '' END +
    R1.ReasonText AS TreePath
FROM CLG_CallReasonTree R1
WHERE R1.ID = @ipid

I'm sure there must be a way to do it more simply, using recursion but just can't manage it.

Look up recursive cte. There are thousands of examples of how to use a recursive cte for this type of thing. — Sean Lange, Mar 17 '16 at 16:29
Here is one of my answers covering a recursive CTE: http://stackoverflow.com/a/34333120/5089204 — Shnugo, Mar 17 '16 at 16:58
And here another example: http://stackoverflow.com/a/35550940/5089204 — Shnugo, Mar 17 '16 at 17:01

score 2 · Answer 1 · answered Mar 17 '16 at 17:01

declare @CLG_CallReasonTree table
(
  ID int, 
  ParentID int,
  ReasonText varchar(100)
)

insert into @CLG_CallReasonTree(ID, ParentID, ReasonText)
values
(1,  0,        'Level1A'),
(2,  0,        'Level1B'),
(3,  1,        'Level2AA'),
(4,  1,        'Level2AB'),
(5,  2,        'Level2BA'),
(6,  4,        'Level3ABA')


declare @ID int = 6

/* for single id with for_xml */
;with cteTree as
(
  select t.ID, t.ParentID, t.ReasonText, 1 as lvl
  from @CLG_CallReasonTree t
  where t.ID = @ID

  union all

  select tt.ID, tt.ParentID, tt.ReasonText, t.lvl+1
  from cteTree t
  inner join @CLG_CallReasonTree tt on tt.ID = t.ParentID
)
select stuff(
  (
    select '->' + t.ReasonText
    from cteTree t
    order by t.lvl desc
    for xml path(''), type
  ).value('.', 'varchar(max)'), 1, 2, '')


/* with reversed path - provides ability to apply like filter */
;with cteTree as
(
  select t.ID, t.ParentID, t.ReasonText, 1 as lvl, cast('/' + cast(t.ID as varchar(10)) + '/' as varchar(1000)) as tree_path
  from @CLG_CallReasonTree t
  where t.ParentID = 0

  union all

  select tt.ID, tt.ParentID, tt.ReasonText, t.lvl+1, cast('/' + cast(tt.ID as varchar(10)) + t.tree_path as varchar(1000))
  from cteTree t
  inner join @CLG_CallReasonTree tt on tt.ParentID = t.ID
)
select * 
from cteTree


/* path-like ReasonText */
declare @reftable table
(
  ID int,
  ReasonID int,
  Title varchar(100)
)

insert into @reftable(ID, ReasonID, Title)
values
(1, 3, 'AAA'),
(2, 5, 'BBB'),
(3, 0, 'CCC'),
(4, 1, 'DDD'),
(5, 6, 'FFF')

;with cteTree as
(
  select t.ID, t.ParentID, cast(t.ReasonText as varchar(1000)) as ReasonText
  from @CLG_CallReasonTree t
  where t.ParentID = 0

  union all

  select tt.ID, tt.ParentID, cast(t.ReasonText + '->' + tt.ReasonText as varchar(1000))
  from cteTree t
  inner join @CLG_CallReasonTree tt on tt.ParentID = t.ID
)
select rt.ID, rt.Title, t.ReasonText
from @reftable rt
inner join cteTree t on t.ID = rt.ReasonID

You're so close to being able to use the HierarchyID data type with your tree path query. I absolutely love being able to use it for things like this. — Ben Thul, Mar 17 '16 at 19:15
What a fantastic answer - thank you. I thought I was ok at SQL, but this shows I have a lot to learn from proper experts. I only signalled the next one as solved, because I also want to be able to select the whole list like that example but this is answer is just brilliant. — Chris H, Mar 18 '16 at 13:11
+1 Very thorough. The only thing it lacks is cited references to help people less familiar with this understand how the various approaches work. — JamieSee, Mar 21 '16 at 16:33

JamieSee · Accepted Answer · 2016-03-17T17:09:23.363

What you are looking for is a recursive CTE of the form below. The nutshell summary is that the query repeats until it hits the limit set in the anchor definition. See Recursive Queries Using Common Table Expressions for more information on how it works.

;WITH CallReasonTree (ID, ParentID, ReasonText, TreePath)
AS
(
-- Anchor member definition
    SELECT ChildReason.ID, ChildReason.ParentID, ChildReason.ReasonText,
        CONVERT(nvarchar(MAX), ChildReason.ReasonText) AS TreePath
    FROM CLG_CallReasonTree AS ChildReason
    WHERE ChildReason.ParentID = 0
    UNION ALL
-- Recursive member definition
    SELECT ChildReason.ID, ChildReason.ParentID, ChildReason.ReasonText, 
        TreePath + '>' + ChildReason.ReasonText AS TreePath
    FROM CLG_CallReasonTree AS ChildReason
    INNER JOIN CallReasonTree AS ParentReason ON ChildReason.ParentID = ParentReason.ID
)
-- Statement that executes the CTE
SELECT ID, ParentID, ReasonText, TreePath
FROM CallReasonTree
ORDER BY ID

This is the resulting output:

ID  ParentID  ReasonText  TreePath
1   0         Level1A     Level1A
2   0         Level1B     Level1B
3   1         Level2AA    Level1A>Level2AA
4   1         Level2AB    Level1A>Level2AB
5   2         Level2BA    Level1B>Level2BA
6   4         Level3ABA   Level1A>Level2AB>Level3ABA

Thanks, that is really excellent and exactly what I was looking for. — Chris H, Mar 18 '16 at 13:12

SQL - Flattening a tree view

2 Answers2