How do I iterate through hierarchical data in a Sql Server 2005 stored proc?

Question

Hi,

I have a SqlServer2005 table "Group" similar to the following:

Id (PK, int)
Name (varchar(50))
ParentId (int)

where ParentId refers to another Id in the Group table. This is used to model hierarchical groups such as:

Group 1 (id = 1, parentid = null)
    +--Group 2 (id = 2, parentid = 1)
    +--Group 3 (id = 3, parentid = 1)
           +--Group 4 (id = 4, parentid = 3)
Group 5 (id = 5, parentid = null)
    +--Group 6 (id = 6, parentid = 5)

You get the picture

I have another table, let's call it "Data" for the sake of simplification, which looks something like:

Id (PK, int)
Key (varchar)
Value (varchar)
GroupId (FK, int)

Now, I am trying to write a stored proc which can get me the "Data" for a given group. For example, if I query for group 1, it returns me the Key-Value-Pairs from Data where groupId = 1. If I query for group 2, it returns the KVPs for groupId = 1, then unioned with those which have groupId = 2 (and duplicated keys are replaced).

Ideally, the sproc would also fail gracefully if there is a cycle (ie if group 1's parent is group 2 and group 2's parent is group 1)

Has anyone had experience in writing such a sproc, or know how this might be accomplished?

Thanks guys, much appreciated,

Alex

Answer 1

Check out this article about Trees and Hierachies

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Answer 2

This can be accomplished using recursive queries and CTEs

;WITH KeyValue AS
(
SELECT G.Id, G.Name, D.Key, D.Value, 0 AS level
FROM Group G
LEFT JOIN Data D ON G.Id = D.GroupID
WHERE G.Id = @your_top_level_root

UNION ALL

SELECT G.Id, G.Name, D.Key, D.Value, K.level + 1
FROM KeyValue K
JOIN Group G ON G.ParentId = K.Id
LEFT JOIN Data D ON G.Id = D.GroupID
)
SELECT * FROM KeyValue