具有不带id的自联接的CTE递归语句

Question

我必须拆分数据列，并与员工和经理建立关系。

表经理详细信息：

>td style="text-align:center；">imp4

数据	员工	经理
imp/imp2/imp3/imp4	imp2	notimp4
notimp1/notimp2/notimp3/notimp4	imp3

如果我正确理解问题，您只需要一个LAG()和递归CTE:中分隔符的位置

表：

CREATE TABLE ManagerDetails (id int, data varchar(1000))
INSERT INTO ManagerDetails (id, data)
VALUES 
(1, 'imp/imp2/imp3/imp4'),
(2, 'notimp1/notimp2/notimp3/notimp4')

声明：

DECLARE @separator varchar(1) = '/';
WITH rCTE AS(
SELECT
d.id,
1 as [level] ,
d.data,
CAST(1 AS int) AS index1,
CHARINDEX(@separator, CONCAT(d.data, @separator)) AS index2
FROM ManagerDetails d   
UNION ALL
SELECT
r.id,
r.[level] + 1,
r.data,
CAST(r.index2 + LEN(@separator) AS int),
CHARINDEX(@separator, CONCAT(r.data, @separator), r.index2 + 1)
FROM rCTE r
WHERE CHARINDEX(@separator, CONCAT(r.data, @separator), r.index2 + 1) > 0
)
SELECT
id AS Id,
SUBSTRING(CONCAT(data, @separator), 1, index2 - 1) AS [Value],
LAG(SUBSTRING(CONCAT(data, @separator), index1, index2 - index1)) OVER (PARTITION BY id ORDER BY level DESC) AS [Manager],
SUBSTRING(CONCAT(data, @separator), index1, index2 - index1) AS [Employee]
FROM rCTE
ORDER BY id, level DESC
OPTION (MAXRECURSION 0)

结果：

Id Value                           Manager Employee
---------------------------------------------------
1  imp/imp2/imp3/imp4                      imp4
1  imp/imp2/imp3                   imp4    imp3
1  imp/imp2                        imp3    imp2
1  imp                             imp2    imp
2  notimp1/notimp2/notimp3/notimp4         notimp4
2  notimp1/notimp2/notimp3         notimp4 notimp3
2  notimp1/notimp2                 notimp3 notimp2
2  notimp1                         notimp2 notimp1