SQL Server层次结构一行到多列查询 [英] SQL Server hierarchy one row to multiple column query

问题描述

我有一个具有这样的结构和记录的表

I have a table with structure and record like this

ID          | Name                      |
-----------------------------------------
01          | Group Category            |
0101        | Category One              |
010101      | Category One Sub          |
01010101    | Category One Sub Sub      |
010102      | Category One Sub Two      |
01010201    | Category One Sub Sub Two  |
0102        | Category Two              |
010201      | Category Two Sub          |
01020101    | Category Two Sub Sub      |
0103        | Category Three            |
010301      | Category Three Sub        |
01030101    | Category Three Sub Sub    |
-----------------------------------------

如何使用SQL Server生成查询结果，如下所示.

How to make a query result with SQL Server like below.

Column1         | Column2       | Column3               | Column4                   |
-------------------------------------------------------------------------------------
Group Category  | Category One  | Category One Sub      | Category One Sub Sub      |
-------------------------------------------------------------------------------------
Group Category  | Category One  | Category One Sub Two  | Category One Sub Sub Two  |
-------------------------------------------------------------------------------------
Group Category  | Category Two  | Category Two Sub      | Category Two Sub Sub      |
-------------------------------------------------------------------------------------
Group Category  | Category Three| Category Three Sub    | Category Three Sub Sub    |
-------------------------------------------------------------------------------------

推荐答案

具有所示的表结构，并且如果级别数固定，则可以使用ID列上的LIKE作为联接条件，简单地多次自我联接:

With table structure as shown, and if number of levels is fixed, you can simply self join multiple times using LIKE on ID column as a join condition:

select t1.name, t2.name, t3.name, t4.name
from #t t4
join #t t3 on t4.id like t3.id+'__'
join #t t2 on t3.id like t2.id+'__'
join #t t1 on t2.id like t1.id+'__'

这应该为您提供所需的输出.

This should give you the desired output.

这篇关于SQL Server层次结构一行到多列查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！