使用TSQL从平面数据创建分层数据

Question

给出了包含以下数据的表格：

Country     City
---------------------
Afghanistan Kabul
Afghanistan Kandahar
Australia   Canberra
Australia   Sydney
Belgium     Antwerp
Belgium     Bruges
Belgium     Brussels

我可以在一个sql语句中返回以下数据吗？

ID  ParentID    Level   Letter  Country         City
---------------------------------------------------------
1   NULL        0       A       NULL            NULL
2   1           1       NULL    Afghanistan     NULL
3   2           2       NULL    NULL            Kabul
4   2           2       NULL    NULL            Kandahar
6   1           1       NULL    Australia       NULL
7   6           2       NULL    NULL            Canberra
8   6           2       NULL    NULL            Sydney
9   NULL        0       B       NULL            NULL
10  9           1       NULL    Belgium         NULL
11  10          2       NULL    NULL            Antwerp
12  10          2       NULL    NULL            Bruges
13  10          2       NULL    NULL            Brussels

Answer 1

Declare @YourTable table (Country varchar(50), City varchar(50))
Insert Into @YourTable values
('Afghanistan','Kabul'),
('Afghanistan','Kandahar'),
('Australia'  ,'Canberra'),
('Australia'  ,'Sydney'),
('Belgium'    ,'Antwerp'),
('Belgium'    ,'Bruges'),
('Belgium'    ,'Brussels')

; with cte0 as (
                Select Lvl0 = cast(Left(Country,1) as varchar(50))
                      ,Lvl1 = cast(Country as varchar(50))
                      ,Lvl2 = cast(City as varchar(50))
                      ,ID0  = Dense_Rank() over (Order By Left(Country,1))
                      ,ID1  = Dense_Rank() over (Order By Country) + 100
                      ,ID2  = Dense_Rank() over (Order By City) + 10000
                 From  @YourTable
               )
Select Distinct ID=ID0,ParentID=null,Level=0,Letter=Lvl0,Country=null,City=null,Path=concat('',Lvl0) from cte0
Union All
Select Distinct ID=ID1,ParentID=ID0,Level=1,Letter=null,Country=Lvl1,City=null,Path=concat('',Lvl0,' > ',Lvl1) from cte0
Union All
Select Distinct ID=ID2,ParentID=ID1,Level=2,Letter=null,Country=null,City=Lvl2,Path=concat('',Lvl0,' > ',Lvl1,' > ',Lvl2) from cte0
Order By Path

返回

​

​