扁平层次

Question

我有下表：

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);

预期结果

​

我想得到分隔列中的层次结构。

这是我尝试过的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;

问题是，例如，第1行--我想得到geo level1 EMEA，但是我得到null。我怎样才能改正呢？

Answer 1

您可以使用CHOOSE根据“级别”返回您想要的值，该值是通过COUNT获取的，该值是由GEO的非NULL值数获得的。

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

Answer 2

另一种方法是尝试使用CTE递归扁平化层次结构，CTE递归与自连接获取所有GEO层次结构，这可能有助于您减少太多的OUTER JOIN，如果您的层次结构级别超过2。

通过添加新的条件聚合函数，我们可以很容易地创建一个新的级别。

;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

木琴