How Can I Efficiently Retrieve Hierarchical Data in SQL Server 2005 Without CONNECT_BY?

Retrieving Hierarchical Data from SQL Server 2005

With the absence of the familiar CONNECT_BY clause in SQL Server 2005, retrieving hierarchical data can pose challenges. One common approach involves creating a recursive common table expression (CTE) that traverses the hierarchy and constructs the desired output.

Consider the example of a self-referencing table containing a hierarchy of objects, where each child record has a column with its parent's ID. A CTE can be used to retrieve the hierarchical relationships 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

By utilizing a CTE, we can recursively traverse the hierarchy, accumulating the path for each item, and easily retrieve the desired hierarchical data in a structured manner. This approach provides a flexible and efficient solution for managing and querying hierarchical data in SQL Server 2005.

The above is the detailed content of How Can I Efficiently Retrieve Hierarchical Data in SQL Server 2005 Without CONNECT_BY?. For more information, please follow other related articles on the PHP Chinese website!