SQL Server-将最后两列的值移动到具有排名的行中

Question

这是我的表，名为"#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');

|population_id|web_id_parent|level_id_parent|web_id_child|level_id_child|
|:------------|:-----------:|:-------------:|:----------:|-------------:|
|840          |10141        |2              |18399636    |3             |
|840          |10141        |2              |3300681     |3             |
|840          |10141        |2              |7112360     |3             |
|840          |11937        |2              |11938       |3             |
|840          |11937        |2              |26068       |3             |

我想要的最终结果表如下：

|population_id|web_id       |level_id       |
|:------------|:-----------:|--------------:|
|840          |10141        |2              |
|840          |18399636     |3              |
|840          |3300681      |3              |
|840          |7112360      |3              |
|840          |11937        |2              |
|840          |11938        |3              |
|840          |26068        |3              |

对于每个web_id_pparent，顺序也按level_id从最小到最大进行排序。

我尝试了unpivot方法，但没有返回我想要的输出。

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

有更好的解决方案吗？

Answer 1

这有帮助吗？

select DISTINCT t.* FROM(
SELECT population_id, web_id, level_id
FROM (SELECT * FROM #test)p
UNPIVOT(
web_id for level in (web_id_parent,web_id_child)
) pvt1
UNPIVOT(
level_id for web in (level_id_parent,level_id_child)
) pvt2 
where RIGHT(level,1) = RIGHT(web,1)
)t

db fiddle