Иерархические данные с CTE

Прошу прощения, если ответ уже опубликован, но я не смог найти ответ даже после поиска.

У меня есть следующая таблица

CREATE TABLE [dbo].[emp]( [id] [int] NOT NULL, [name] [varchar](20) NULL, [mgrid] [int] NULL ) ON [PRIMARY] 

С данными

 INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (1, N'a', 0) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (2, N'a1', 1) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (3, N'a11', 2) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (4, N'a12', 2) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (5, N'a13', 2) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (6, N'a2', 1) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (7, N'a3', 1) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (8, N'a31', 7) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (9, N'a32', 7) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (10, N'b', 0) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (11, N'b1', 10) INSERT [dbo].[emp] ([id], [name], [mgrid]) VALUES (12, N'b2', 10) 

И я хотел бы получить следующий результат

 a a1 a11 a12 a13 a2 a3 a31 a32 b b1 b2 

Возможно ли это в SQL Server?

Демоверсия SQLFiddle

Вы можете использовать '' вместо '+' в последней строке для ввода строки.

 with t as ( select id,name,mgrid,1 as level,cast(name as varchar(max)) as path from emp where mgrid=0 union all select emp.id,emp.name,emp.mgrid, t.level+1 as level, t.path+cast(emp.name as varchar(max)) as path from emp join t on emp.mgrid=t.id ) select replicate('+', level)+name from t order by path 
 with CTE as ( select * , level = 1 from emp where mgrid = 0 union all select emp.id, name = cast(space((level) * 3) + emp.name as varchar(20)), emp.mgrid, level = level + 1 from emp inner join CTE on CTE.id = emp.mgrid ) select name from CTE order by ltrim(name) 

Запрос:

 DECLARE @temp TABLE ( [id] [int] NOT NULL, [name] [varchar](20) NULL, [mgrid] [int] NULL ) INSERT INTO @temp ([id], [name], [mgrid]) VALUES (1, N'a', 0), (2, N'a1', 1), (3, N'a11', 2), (4, N'a12', 2), (5, N'a13', 2), (6, N'a2', 1), (7, N'a3', 1), (8, N'a31', 7), (9, N'a32', 7), (10, N'b', 0), (11, N'b1', 10), (12, N'b2', 10) DECLARE @out VARCHAR(MAX) = '' ;WITH cte AS ( SELECT *, Lvl = 0, nn = CAST(name AS VARCHAR(MAX)) FROM @temp WHERE mgrid = 0 UNION ALL SELECT t.*, c.Lvl + 1, nn = CAST(REPLICATE(' ', c.Lvl + 1) + t.name AS VARCHAR(MAX)) FROM @temp t JOIN cte c ON c.id = t.mgrid ) SELECT @out = ( SELECT nn + CHAR(13) FROM cte ORDER BY LTRIM(nn) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') PRINT @out 

Вывод:

 a a1 a11 a12 a13 a2 a3 a31 a32 b b1 b2 

Вы можете использовать CTE (выражение таблицы Comnon для достижения этого)

проверьте этот пример кода:

 declare @emp table( [id] [int] NOT NULL, [name] [varchar](20) NULL, [mgrid] [int] NULL ) INSERT @emp ([id], [name], [mgrid]) VALUES (1, N'a', 0) INSERT @emp ([id], [name], [mgrid]) VALUES (2, N'a1', 1) INSERT @emp ([id], [name], [mgrid]) VALUES (3, N'a11', 2) INSERT @emp ([id], [name], [mgrid]) VALUES (4, N'a12', 2) INSERT @emp ([id], [name], [mgrid]) VALUES (5, N'a13', 2) INSERT @emp ([id], [name], [mgrid]) VALUES (6, N'a2', 1) INSERT @emp ([id], [name], [mgrid]) VALUES (7, N'a3', 1) INSERT @emp ([id], [name], [mgrid]) VALUES (8, N'a31', 7) INSERT @emp ([id], [name], [mgrid]) VALUES (9, N'a32', 7) INSERT @emp ([id], [name], [mgrid]) VALUES (10, N'b', 0) INSERT @emp ([id], [name], [mgrid]) VALUES (11, N'b1', 10) INSERT @emp ([id], [name], [mgrid]) VALUES (12, N'b2', 10); with cte (id,name, MGRID) as ( select id, name,MGRID from @emp union all select c.id, c.name, c.MGRID from @emp c inner join cte p on c.mgrid = P.id ) SELECT Distinct * FROM CTE 
 WITH RCTE AS ( SELECT id, name, mgrid, CAST('' AS NVARCHAR(MAX)) AS blanks , CAST(name AS NVARCHAR(MAX)) AS ordr FROM dbo.emp WHERE mgrid = 0 UNION ALL SELECT e.id, e.name, e.mgrid, blanks + ' ' AS blanks, ordr + e.name AS ordr FROM dbo.emp e INNER JOIN RCTE r ON e.mgrid = r.id ) SELECT blanks + name FROM RCTE ORDER BY ordr 
Interesting Posts
Давайте будем гением компьютера.