Friday, October 21, 2011

SQL Query – How to get data in Hierarchical Structure?

Download sample data with Schema
Prepare an SQL query to display the given data in “Hierarchical Structure” based on the level of the parent node. In a way that tree structure can be prepared.

I have one table:
  • Location – It contains the data with Parent – Child relationship.

The structure of the table and result set is as follows:

Location
Resultant Data

Please provide your SQL query in comment of this article.

Click here to download the sample data including schema.

Following are the solutions provided by:
Me (Chirag Vidani)
WITH LocationList (LocationId,LocationName,ParentLocationId,LocationLevel)
AS
(
    SELECT loc.Id, loc.Name,loc.ParentId, 1 AS LocationLevel
    FROM Location AS loc
    WHERE loc.ParentId IS NULL
    UNION ALL
    SELECT l.Id,l.Name,l.ParentId,list.LocationLevel + 1
    FROM Location AS l
    INNER JOIN LocationList AS list
        ON l.ParentId = list.LocationId
)
SELECT LocationId,LocationName,isnull(ParentLocationId,0) 'ParentLocationId',LocationLevel FROM LocationList


Mr. Kaushik Patel
WITH LocationLevelReports (LocationID, Locationname , ParentLocationId, LocationLevel)
AS
(
-- Anchor member definition
SELECT L.ID, L.name, ISNULL(L.ParentId,0) ParentId, 
1 AS LocationLevel
FROM Location AS L
WHERE ParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT L.ID, L.name, L.ParentId, 
LocationLevel + 1 as LocationLevel
FROM Location AS L
INNER JOIN LocationLevelReports AS R
ON L.ParentId = R.LocationID

)
select * from LocationLevelReports



Learn by diving in Programming Ocean...
Happy Programming!!!

1 comment:

  1. Please use following Query:

    WITH LocationLevelReports (LocationID, Locationname , ParentLocationId, LocationLevel)
    AS
    (
    -- Anchor member definition
    SELECT L.ID, L.name, ISNULL(L.ParentId,0) ParentId,
    1 AS LocationLevel
    FROM Location AS L
    WHERE ParentId IS NULL
    UNION ALL
    -- Recursive member definition
    SELECT L.ID, L.name, L.ParentId,
    LocationLevel + 1 as LocationLevel
    FROM Location AS L
    INNER JOIN LocationLevelReports AS R
    ON L.ParentId = R.LocationID

    )
    select * from LocationLevelReports

    ReplyDelete