Hierarchical Queries in SQL Server 2005

Question

Way back when I was working in an Oracle shop I took the CONNECT_BY for granted. Now I'm stuck working with SQL Server 2005 and have some nasty object hierarchies. Specifically, we have a self referencing table where all child records have a column with their parent's id. Currently we have a view that maps children to levels in the hierarchy and a nasty query that does the heavy lifting to connect parents with their children. While this method works, it is far from elegant and reeks of taint. I'm just curious how other people retrieve hierarchical data from SQL Server 2005.

Answer 1

This creates your typical hierarchical table and uses a CTE to select the hierarchy structure and create a path for each item.

CREATE TABLE tblHierarchy (ID int, ParentID int NULL, Name varchar(128));

INSERT INTO tblHierarchy VALUES (1, NULL, '1');
INSERT INTO tblHierarchy VALUES (2, NULL, '2');
INSERT INTO tblHierarchy VALUES (3, NULL, '3');
INSERT INTO tblHierarchy VALUES (4, 1, '1.1');
INSERT INTO tblHierarchy VALUES (5, 1, '1.2');
INSERT INTO tblHierarchy VALUES (6, 4, '1.1.1');

WITH Parent AS
(
    SELECT
        ID,
        ParentID,
        Name AS Path
    FROM
        tblHierarchy
    WHERE
        ParentID IS NULL

    UNION ALL

    SELECT
        TH.ID,
        TH.ParentID,
        CONVERT(varchar(128), Parent.Path + '/' + TH.Name) AS Path
    FROM
        tblHierarchy TH
    INNER JOIN
        Parent
    ON
        Parent.ID = TH.ParentID
)
SELECT * FROM Parent

OUTPUT:

ID  ParentID    Path
1   NULL        1
2   NULL        2
3   NULL        3
4   1       1/1.1
5   1       1/1.2
6   4       1/1.1/1.1.1

Answer 2

Having used both, I found CONNECT BY is somewhat more flexible and easier to use than CTE's. The question is not dissimilar to one I answered a few weeks ago. See Here for a brief comparison of CONNECT BY and CTE's and Here for an example of a query using CTE's.