Sql Query For Returning Hierarchical Data

When we need such type of return from a stored procedure which has a parent child relationship and a level (To bind a treeview or a dropdownlist) we need to know about CTE  its help us to do the same. So here I'm showing you how could we iplemet this as per our requirement.

 Recursive Queries Using Common Table Expressions(CTE)

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
 EmployeeID smallint NOT NULL,
 FirstName nvarchar(30)  NOT NULL,
 LastName  nvarchar(40) NOT NULL,
 Title nvarchar(50) NOT NULL,
 DeptID smallint NOT NULL,
 ManagerID int NULL,
 CONSTRAINT PK_EmployeeID 

PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', 

N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', 

N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', 

N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', 

N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', 

N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', 

N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', 

N'Sales Representative',3,285)
,(16,  N'David',N'Bradley',

N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', 

N'Marketing Specialist', 4, 16);

 -----------------------------------------------------------------------------
CREATE proc [dbo].[sp_ReturningHierarchicalData]
AS
--Recursive Queries Using Common Table Expressions
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT
        e.ManagerID ,
        e.EmployeeID,
        e.Title,
        e.DeptID,
        0 AS Level
    FROM
        MyEmployees AS e
    WHERE
        e.ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT
        e.ManagerID,
        e.EmployeeID,
        e.Title,
        e.DeptID,
        Level + 1
    FROM
        MyEmployees AS e
    INNER JOIN
        DirectReports AS d
    ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT
    ManagerID,
    EmployeeID,
    --Case
    --    When Level = 0 Then  Title ----level 0
    --    When Level = 1 Then  convert(nvarchar(50),'  ' + Title)----level 1
    --    When Level = 2 Then  convert(nvarchar(50),'    ' + Title)----level 2
    --                    Else convert(nvarchar(50),'      ' + Title)----level 3
    --End as Title,
    Title,
    DeptID,
    Level
FROM DirectReports
-------------------

 Result

------------------------------------------------------------------------------------------
ManagerID   EmployeeID    Title                                  DeptID      Level
----------- ---------- ----------------------------- --------------------------------------
NULL        1                      Chief Executive Officer               16              0
1                273                  Vice President of Sales               3               1
273            16                    Marketing Manager                      4                2
273            274                  North American Sales Manager  3                2
273            285                  Pacific Sales Manager                3                2
285            286                  Sales Representative                  3                3
274            275                  Sales Representative                  3                3
274            276                  Sales Representative                  3                3
16              23                    Marketing Specialist                    4                3
--------------------------------------------------------------------------------------------
More details : http://msdn.microsoft.com/en-us/library/ms186243.aspx

 Hope this helps you , i got this from the link above as an query and i implement this as stored procedure.

Related Posts: