Tree view using SQL Query

Question

I have a regions table of which I want a tree view (table simple ordered as tree) is it possible using sql queries help is appreciated, I tried to do it using self joins but i did not get the desired result.

tree view is something like this

Indiv

• Div1
• • Zon1
• div2
• • zon2
• div3
• • zon3

EDIT:

as per Charles Bretana suggetion I tried CTE in below query and it did not give me desired result.

WITH Emp_CTE (id, ParentID, name)
AS (
SELECT id, ParentID, name
FROM eQPortal_Region
WHERE ParentID=0
UNION ALL
SELECT e.id, e.ParentID, e.name
FROM eQPortal_Region e
INNER JOIN Emp_CTE ecte ON ecte.id = e.ParentID
)
SELECT *
FROM Emp_CTE
GO

This is the result .. what went wrong ?

InDiv1

• Div1
• Div2
• Div3
• • Zon3
• • Zon2
• • zon1

Answer 1

See the following sample SQL script:

DECLARE
    @lV NCHAR(1)=NCHAR(9474),
    @lR NCHAR(1)=NCHAR(9500),
    @lU NCHAR(1)=NCHAR(9492),
    @spc NCHAR(1)='　';
WITH
    data AS
    (
        SELECT * FROM(VALUES
            (1,'0','In Div1',1),
            (2,'1','Div1',1),
            (3,'2','zon1',1),
            (4,'1','Div2',2),
            (5,'1','Div3',3),
            (6,'4','Zon2',1),
            (7,'5','Zon3',1)
        )t([ID],[ParentID],[Name],[SeqOrder])
    ),
    d_root AS
    (
        SELECT CAST('/'AS NVARCHAR(MAX))[n_path],*
        FROM data WHERE ParentID='0'
    ),
    d_cte AS
    (
        SELECT b.n_path+CAST(ROW_NUMBER()OVER(ORDER BY a.ID) AS NVARCHAR(MAX))+'/'[n_path],a.*
        FROM data a
        INNER JOIN d_root b ON a.ParentID=b.ID
            UNION ALL
        SELECT b.n_path+CAST(ROW_NUMBER()OVER(ORDER BY a.ID) AS NVARCHAR(MAX))+'/'[n_path],a.*
        FROM data A
        INNER JOIN d_cte b ON a.ParentID=b.ID
    ),
    Tree_BASE AS
    (
        SELECT CAST(n_path AS HIERARCHYID)[h_id],*
        FROM d_root
            UNION ALL
        SELECT CAST(n_path AS HIERARCHYID)[h_id],*
        FROM d_cte
    ),
    cte_o AS
    (
        SELECT
            a.h_id,
            CASE
                WHEN EXISTS(SELECT * FROM Tree_BASE WHERE h_id>b.h_id AND h_id.GetAncestor(1)=b.h_id.GetAncestor(1)) THEN @lV
                ELSE @spc
            END [t_l]
        FROM Tree_BASE a
        INNER JOIN Tree_BASE b ON a.h_id.IsDescendantOf(b.h_id)=1 AND NOT b.h_id=HIERARCHYID::GetRoot()
    ),
    cte_m AS
    (
        SELECT
            x.*,
            (SELECT t_l+'' FROM cte_o WHERE h_id=x.h_id FOR XML PATH(''))[b_tree]
        FROM Tree_BASE x
    ),
    cte_h AS
    (
        SELECT
            ISNULL
            (
                LEFT(b_tree,LEN(b_tree)-1)
                +
                CASE
                    WHEN EXISTS(SELECT * FROM Tree_BASE WHERE h_id>a.h_id AND h_id.GetAncestor(1)=a.h_id.GetAncestor(1)) THEN @lR
                    ELSE @lU
                END,
                ''
            )
            +' '
            +RTRIM(Name)
            [TreeV],
            a.h_id
        FROM cte_m a
    )
SELECT b.TreeV,ROW_NUMBER()OVER(ORDER BY a.h_id ASC)[row_id]
FROM Tree_BASE a
INNER JOIN cte_h b ON a.h_id=b.h_id
ORDER BY row_id;

Answer 2

This guy Maulik Dhorajia answers the question perfectly ...

http://maulikdhorajia.blogspot.com/2012/06/sql-server-using-ctecommon-table.html

Made a replica of the query ..

;WITH CTECompany
AS
(
    SELECT 
    ID, 
    ParentID, 
    Name , 
    0 AS HLevel,
    CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),ID),20) AS VARCHAR(MAX)) AS OrderByField
FROM Region
WHERE ParentID = 0

UNION ALL

SELECT 
    C.ID, 
    C.ParentID, 
    C.Name , 
    (CTE.HLevel + 1) AS HLevel,
    CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.ID),20) AS VARCHAR(MAX)) AS OrderByField
FROM Region C
INNER JOIN CTECompany CTE ON CTE.ID = C.ParentID
WHERE C.ParentID IS NOT NULL


)

-- Working Example
SELECT 
ID
, ParentID
, HLevel
, Name
, (REPLICATE( '----' , HLevel ) + Name) AS Hierachy
FROM CTECompany
ORDER BY OrderByField