SQL Server：将父子列转换为带排序的行结构

我有一张名为#test的临时表，表结构及数据如下：

create table #test 
(
population_id int,
web_id_parent int,
level_id_parent int,
web_id_child int,
level_id_child int
);

insert into #test (population_id, web_id_parent, level_id_parent, web_id_child, level_id_child) values ('840','10141','2','18399636','3');
insert into #test (population_id, web_id_parent, level_id_parent, web_id_child, level_id_child) values ('840','10141','2','3300681','3');
insert into #test (population_id, web_id_parent, level_id_parent, web_id_child, level_id_child) values ('840','10141','2','7112360','3');
insert into #test (population_id, web_id_parent, level_id_parent, web_id_child, level_id_child) values ('840','11937','2','11938','3');
insert into #test (population_id, web_id_parent, level_id_parent, web_id_child, level_id_child) values ('840','11937','2','26068','3');

当前表数据：

期望得到的最终表结构：

要求每个web_id_parent对应的行按level_id从小到大排序。我尝试了UNPIVOT方法，但未得到预期结果：

select * from #test unpivot(web_id for level_id in ([web_id_parent],[web_id_child])) as #unpivot_table;

请问有更好的解决方案吗？

你的UNPIVOT写法问题在于没有正确关联对应的level_id，只拆分了web_id，导致level_id列变成了列名（web_id_parent/web_id_child）而非实际的层级数值。可以用UNION ALL来实现需求，同时处理排序逻辑：

WITH combined_data AS (
    -- 取出父节点数据
    SELECT 
        population_id,
        web_id_parent AS web_id,
        level_id_parent AS level_id,
        web_id_parent AS group_key
    FROM #test
    UNION ALL
    -- 取出子节点数据
    SELECT 
        population_id,
        web_id_child AS web_id,
        level_id_child AS level_id,
        web_id_parent AS group_key
    FROM #test
)
SELECT 
    population_id,
    web_id,
    level_id
FROM combined_data
GROUP BY population_id, web_id, level_id, group_key
ORDER BY 
    group_key,
    level_id,
    web_id;

说明： #

1. UNION ALL分别提取父节点和子节点的对应字段，用group_key标记每个节点所属的父节点组，方便后续按父节点分组排序。
2. GROUP BY用于去重——原表中同一个父节点对应多个子节点，父节点会被重复取出多次，去重后只保留一条父节点记录。
3. ORDER BY先按group_key（父节点web_id）分组，再按level_id从小到大排序，最后按web_id排序保证结果顺序稳定。

如果你的SQL Server版本支持，也可以把去重放在CTE中：

WITH combined_data AS (
    SELECT DISTINCT
        population_id,
        web_id_parent AS web_id,
        level_id_parent AS level_id,
        web_id_parent AS group_key
    FROM #test
    UNION ALL
    SELECT 
        population_id,
        web_id_child AS web_id,
        level_id_child AS level_id,
        web_id_parent AS group_key
    FROM #test
)
SELECT population_id, web_id, level_id
FROM combined_data
ORDER BY group_key, level_id, web_id;

这样就能得到期望的结果，且满足排序要求。

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