Friday, June 8, 2012

SQL Server : Using CTE(Common Table Expression) fetch a Tree View

Goal

Using CTE show a complete tree view.

Challenge

When a developer tries to search CTE in internet, there are millions of articles which already exists which says to get a tree view using CTE. But there is a problem with most of them. They dont give correct output. For making the data show in correct way users should enter data in an order, else it wont come. So first I will explain how the examples on internet mislead.


SQL - Create temp table

-- DECLARE
DECLARE @Company AS TABLE(EmpID INT, ParentID INT, PersonName VARCHAR(100));

-- Insert Temp Records
INSERT INTO @Company(EmpID, ParentID, PersonName) 
VALUES(1 , NULL , 'Maulik Dhorajia')
    , (2 , NULL , 'Bhavesh Gohel')
    , (3 , NULL , 'Dinesh Padhiyar')
    , (4 , 2 , 'Vijay Kumar')
    , (5 , 1 , 'Jitendra Makwana')
    , (6 , 4 , 'Jayesh Dhobi')
    , (7 , 1 , 'Shivpalsinh Jhala')
    , (8 , 5 , 'Amit Patel')
    , (9 , 3 , 'Abidali Suthar')

-- Default data
SELECT * FROM @Company;



SQL - Which usually misleads to get correct tree view

-- Incorrect result which we usually find on Internet
;WITH CTECompany
AS
(
    SELECT EmpID, ParentID, PersonName , 0 AS HLevel 
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT C.EmpID, C.ParentID, C.PersonName , (CTE.HLevel + 1) AS HLevel 
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Misleading SQL
SELECT * FROM
(
    SELECT 
        EmpID
        , ParentID
        , HLevel
        , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
    FROM CTECompany
) AS P
ORDER BY HLevel;




SQL - Correct CTE with Tree View

-- Working Example
;WITH CTECompany
AS
(
    SELECT 
        EmpID, 
        ParentID, 
        PersonName , 
        0 AS HLevel,
        CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        C.EmpID, 
        C.ParentID, 
        C.PersonName , 
        (CTE.HLevel + 1) AS HLevel,
        CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Working Example
SELECT 
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM CTECompany
ORDER BY OrderByField,PersonName;


Hope this helped and saved a lot of your time!

Reply for the comment "Nice try. still relies on order of data entry. add (44 , 1 , 'XXitendra Makwana')". I tried to see what was wrong but I can see the correct tree with the same query. Not sure what is wrong. Can anyone else see the issue and post a comment? Thanks in advance.

15 comments:

  1. Thank you for sharing. Very helpful!

    ReplyDelete
  2. This was the cherry that pulled me out of a very frustrating day! Thanks!

    ReplyDelete
  3. Good Job, Thanks!!

    ReplyDelete
  4. This was not the cherry that pulled me out of a very frustrating day! Thanks!

    ReplyDelete
  5. Nice try. still relies on order of data entry. add (44 , 1 , 'XXitendra Makwana')

    ReplyDelete
    Replies
    1. I was not able to see any issue. The tree was forming correctly. I don't see what the issue is? Please put in more details. I can fix it if there is any trouble. Also the example is just to create a tree view.

      Delete
  6. Good post... I have one requirement i.e., I want push the above table data in XML as tree structure using sql query... any solution?

    ReplyDelete
    Replies
    1. Not so easily. You will have to write a procedure to create an xml. Better do it using C# or PHP. Can you put in more details? How you want the xml.

      Thanks,
      Maulik Dhorajia

      Delete
  7. For example if I search for 'Amit Patel' , is it possible to display the tree view from root?

    ReplyDelete
    Replies
    1. @Anonymous - I guess this query is not for searching. You will have to modify it. The query is a solution to provide a tree view and not a search.

      Where clause will remove all the records which will not have "Amit Patel". So ultimately the tree will not form. If you are sure at what level you have to filter than its possible.

      In any case you will have to modify the query.

      Delete
  8. This worked well for me. Now to convert result into JSON nested objects...

    ReplyDelete