Custom hierarchy-CodePudding

I've a table source

idGeo     GEO   PARENTID
1   EMEA    NULL
2   France  1
3   mIDCAPSfRANCE   2
4   Germany 1
5   France exl midcaps  2
6   Amercias    NULL
7   US  6

The expected result of the hierarchy

I tried to do left join(self join) but I'm not able to get exactly as expected.

CodePudding user response：

Here is a generic method regardless of the level of the hierarchy.

SQL

-- DDL and sample data population, start
DECLARE @tbl table (
    idGeo INT IDENTITY PRIMARY KEY,
    GEO   VARCHAR(64),
    PARENTID INT
);

insert into @tbl (GEO, PARENTID) values 
(   'EMEA',    NULL),
(   'France',  1),
(   'mIDCAPSfRANCE',   2),
(   'Germany', 1),
(   'France exl midcaps',  2),
(   'Amercias',    NULL),
(   'US',  6);
-- DDL and sample data population, end

--SELECT * FROM @tbl;

WITH cte AS 
(
    -- Anchor query
    SELECT idGEO, GEO, ParentID, 1 AS [Level]
        , CAST('/'   GEO AS VARCHAR(1000)) AS XPath
    FROM @tbl
    WHERE ParentID IS NULL
    UNION ALL
    -- Recursive query
    SELECT t.idGEO, t.GEO, t.ParentID, cte.[Level]   1 AS [Level]
        , CAST(cte.[XPath]   '/'    t.GEO AS VARCHAR(1000)) AS [XPath]
    FROM @tbl AS t
        INNER JOIN cte ON t.ParentID = cte.idGEO
    WHERE t.ParentID IS NOT NULL 
)
SELECT idGEO
    , REPLICATE('  ',[Level]-1)   GEO AS GEOHierarchy
    , GEO, ParentID, [Level], [XPath]
FROM cte
ORDER BY XPath;

Output

CodePudding user response：

So if I understand you want to generate columns as the levels go on. You cannot dynalically generate columns, SQL is a fixed column language.