Search code examples
sqlhierarchy

SQL to retrieve parent-child relationship in parent-child order, from a self referencing table


I use the following query to retrieve the parent-child relationship data, from a table which is self referencing to the parent.

    -- go down the hierarchy and get the childs
    WITH ChildLocations(LocationId, FkParentLocationId, [Level]) 
        AS 
        (
            (
                -- Start CTE off by selecting the home location of the user
                SELECT l.LocationId, l.FkParentLocationId, 0 as [Level]
                FROM   Location l
                WHERE  l.LocationId = @locationId
            )
            UNION ALL 
            -- Recursively add locations that are children of records already found in previous iterations.
            SELECT l2.LocationId, l2.FkParentLocationId, [Level] + 1
            FROM   ChildLocations tmp
                   INNER JOIN Location l2
                        ON  l2.FkParentLocationId = tmp.LocationId
        )
    INSERT INTO @tmp
    SELECT * from ChildLocations;

The table has the following fields: LocationId, FkParentLocationId, FkLocationTypeId, etc...

This works fine, but how I want to retrieve it is as follows:

Parent 1
    Child 1
    Child 2
      Child 21
    Child 3
      Child 31
Parent 2
    Child 4
    Child 5
    Child 6

What is currently gives is like:

Parent 1
Parent 2
    Child 1
    Child 2
    Child 3
    Child 4
 etc....

How can I modify the above to get it in the order I want.


Solution

  • What about to append an 'order' field? This may be an approach:

    WITH ChildLocations(LocationId, FkParentLocationId, [Level]) 
        AS 
        (
            (
                -- Start CTE off by selecting the home location of the user
                SELECT l.LocationId, l.FkParentLocationId, 0 as [Level],
                       cast( str( l.locationId ) as varchar(max) ) as orderField
                FROM   Location l
                WHERE  l.LocationId = @locationId
            )
            UNION ALL 
            -- Recursively add locations that are children ...
            SELECT l2.LocationId, l2.FkParentLocationId, [Level] + 1,
                   tmp.orderField + '-' + 
                   str(tmp.locationId) as orderField
            FROM   ChildLocations tmp
                   INNER JOIN Location l2
                        ON  l2.FkParentLocationId = tmp.LocationId
        )
    SELECT * from ChildLocations order by orderField;
    

    Remember than Order by in an Insert is not allowed.

    Take a look a sample