使用SQL将表中地区/国家/城市列转成行并按orderID排序

现有如下示例表，其中条目列存储地区、国家、城市信息，需通过SQL查询，基于排序ID列的字母顺序，将条目列的内容从列转换为行，得到对应的预期输出表。

示例表 #

预期输出表 #

解决方案SQL #

WITH ranked_entries AS (
    SELECT 
        list,
        orderID,
        entry,
        LENGTH(orderID) AS level
    FROM your_table_name
)
SELECT 
    re.list,
    region.entry AS 地区,
    country.entry AS 国家,
    city.entry AS 城市
FROM ranked_entries re
LEFT JOIN ranked_entries region 
    ON re.list = region.list 
    AND SUBSTRING(re.orderID, 1, 2) = region.orderID 
    AND region.level = 2
LEFT JOIN ranked_entries country 
    ON re.list = country.list 
    AND (
        (re.level = 4 AND SUBSTRING(re.orderID, 1, 3) = country.orderID)
        OR (re.level = 3 AND re.orderID = country.orderID)
    )
    AND country.level = 3
LEFT JOIN ranked_entries city 
    ON re.list = city.list 
    AND re.orderID = city.orderID 
    AND city.level = 4
WHERE re.level IN (3, 4)
ORDER BY re.orderID;

逻辑说明 #

1. 先用CTEranked_entries给每个条目标记层级：排序ID长度为2的是地区，长度3的是国家，长度4的是城市。
2. 通过三次自连接分别匹配每个条目对应的上级地区、所属国家，以及自身作为城市的情况。
3. 最后过滤出国家和城市级别的记录，按照排序ID排序，得到预期的列转行结果。

内容的提问来源于stack exchange，提问作者Reus ellmy