Flattening a SQL hierarchical data structure in SQL without cursors

Question

Let's say I have the following table

SectionID ParentIDSection DatumID
   1           NULL         1
   2            1           2
   3            1           3
   4            2           4

Now, let's pretend I want to select all DatumIDs under SectionID = 1, even if it is part of its descendants children so that I would get

SectionID DatumID
  1         1
  1         2
  1         3
  1         4

Is it possible to this without explicitly iterating over the first table recursively with a cursor?

[Using Common Table Expressions](http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx) — Robert Harvey, Nov 07 '12 at 23:49

score 5 · Accepted Answer · answered Nov 08 '12 at 00:19

This confused me for a minute because it was kind of going the opposite direction of how I think about recursion, but I came up with this solution that is pretty simple:

;WITH Rollups AS (
    SELECT SectionId, ParentIdSection, DatumId
    FROM SectionDataTable
    UNION ALL
    SELECT parent.SectionId, parent.ParentIdSection, child.DatumId
    FROM SectionDataTable parent 
    INNER JOIN Rollups child ON child.ParentIdSection = parent.SectionId
)
SELECT *
FROM Rollups 
WHERE SectionID = 1

(Replace with the section id you want)

JohnLBevan · Answer 2 · 2012-11-08T00:56:42.947

declare @demo table
(
    SectionId int not null primary key clustered
    , ParentId int null --foreign key references @demo(SectionId)
    , DatumId int
);

insert @demo
      select 1, null, 1
union select 2, 1, 2
union select 3, 1, 3
union select 4, 2, 4
union select 5, null, 5
union select 6, 5, 6
;

with demoHierarchy
as
(
    select SectionId
    , SectionId ParentId
    , SectionId OriginalAncestorId
    , DatumId
    from @demo
    where ParentId is null --remove this line if you want OriginalAncestorId to be AnyAncestorId (if that doesn't make sense, try it and see)

    union all

    select d.SectionId
    , d.ParentId
    , h.OriginalAncestorId
    , d.DatumId
    from @demo d
    inner join demoHierarchy h
    on d.ParentId = h.SectionId
    where d.ParentId is not null --implied by the join, but here to make it explicit
)
select OriginalAncestorId SectionId
, DatumId 
from demoHierarchy
where OriginalAncestorId = 1 --not needed given your sample data only contains this data, but required when you have multiple root ancestors

More info on hierarchical queries available here: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

score 1 · Answer 3 · edited May 23 '17 at 12:27

1

You can use the Hierarchy ID in MS SQL 2008. Another question was asked about relative performance at SQL Server 2008 Hierarchy Data Type Performance?.

Here is another reference on handling recursive hierarchies: http://www.bimonkey.com/2009/09/handling-recursive-hierarchies-in-sql-server/

edited May 23 '17 at 12:27

Community

1
1

answered Nov 08 '12 at 00:11

Demitrius Nelon

1,210
1
10
29

Flattening a SQL hierarchical data structure in SQL without cursors

3 Answers3