I've the following table:

create table T (
    idGeo INT IDENTITY(1,1),
    GEO   VARCHAR(64),
    PARENTID INT
);

insert into T (GEO, PARENTID) values 
(   'EMEA',    NULL),
(   'France',  1),
(   'mIDCAPSfRANCE',   2),
(   'Germany', 1),
(   'France exl midcaps',  2),
(   'Amercias',    NULL),
(   'US',  6);

Expected results

I'd like to get hierarchy in separated columns.

Here what I tried https://sqlize.online/sql/mssql2017/7f34918507bae9d9b74af96c5f5e83dc/

select T.idGeo, T.GEO, T1.GEO [GEO Level 1], T2.GEO [GEO Level 2]
from T
left join T T1 on T.PARENTID = T1.idGeo
left join T T2 on T1.PARENTID = T2.idGeo;

The issue is for example line 1 I suppose to get geo level1 EMEA, but I get null. How can I correct it?

CodePudding user response：

You could use CHOOSE to return which value you want, based on the "level", which is obtained by COUNTing the number of non-NULL values of GEO:

SELECT T.idGeo,
       T.GEO,
       CHOOSE(C.Level,T.GEO,T1.GEO,T2.GEO) AS [GEO Level 1],
       CHOOSE(C.Level-1,T.GEO,T1.GEO,T2.GEO) AS [GEO Level 1],
       CHOOSE(C.Level-2,T.GEO,T1.GEO,T2.GEO) AS [GEO Level 2]
FROM dbo.T
     LEFT JOIN dbo.T T1 ON T.PARENTID = T1.idGeo
     LEFT JOIN dbo.T T2 ON T1.PARENTID = T2.idGeo
     CROSS APPLY ((SELECT COUNT(V.GEO) AS Level
                   FROM (VALUES(T.GEO),(T1.GEO),(T2.GEO))V(GEO))) C;

db<>fiddle

CodePudding user response：

Another way you can try to use CTE recursive Flatten hierarchy, CTE recursive with self-join get all GEO hierarchy which might help you reduce too much OUTER JOIN if your hierarchy level is more than 2.

We can easily create a new level by adding a new condition aggregate function.

;WITH CTE AS (
   SELECT idGeo,GEO cGEO,GEO,PARENTID,0 level
   FROM T
   UNION ALL
   SELECT t.idGeo,t.GEO,c.GEO,c.PARENTID,level 1
   FROM CTE c 
   INNER JOIN T t
   ON t.PARENTID = c.idGeo
)
SELECT idGeo,
       cGEO,
       MAX(CASE WHEN rn = 1 THEN GEO END) [GEO Level 1],
       MAX(CASE WHEN rn = 2 THEN GEO END) [GEO Level 2],
       MAX(CASE WHEN rn = 3 THEN GEO END) [GEO Level 3]
FROM (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY idGeo ORDER BY level desc) rn
 FROM CTE
)t1
GROUP BY idGeo,
       cGEO
ORDER BY idGeo

sqlfiddle