mssql层次结构一行到多列查询

I have a table with structure and record like this

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

________________________________________
ID          | Name              |
-----------------------------------------
01          | Group Category    |
0101        | Category      |
010101      | Category Sub      |
01010101    | Category Sub Sub  |
-----------------------------------------

How to make a query result with mssql like bellow.

如何使用像bellow这样的mssql进行查询。

Column1         | Column2       | Column3       | Column4           |
-------------------------------------------------------------------------
Group Category  | Category Sub  | Category Sub  | Category Sub Sub  |
-------------------------------------------------------------------------

2 个解决方案

#1

declare @t table (ID varchar(10),name varchar(20))
insert into @t (ID,name) values ('01','Group Category'),('0101','Category'),
('010101','Category Sub'),('01010101','Category Sub Sub ')



select 
MAX(case when ID = '01' THEN name ELSE '' END )AS Column1,
MAX(case when ID = '0101' THEN name ELSE '' END )AS Column2,
MAX(case when ID = '010101' THEN name ELSE '' END )AS Column3,
MAX(case when ID = '01010101' THEN name ELSE '' END )AS Column4 
from @t

#2

You could also PIVOT your table like this:

你也可以像这样偷看你的桌子：

SELECT [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4
FROM
(
    SELECT 
        Name, 
        ROW_NUMBER() OVER(ORDER BY ID) Seq 
    FROM [Table]
) t
PIVOT
(
    MAX(Name)
    FOR Seq IN ([1], [2], [3], [4])
) p