I've to split the data column and build relationship with employee and the manager.

Table ManagerDetails:

If you observe the below output table, if i take the first row the imp4 is the manager of imp3, imp3 is the manager of imp2, imp2 is manager of imp, so i need to build the table as mentioned below.

Expected output for first row:

I used CTE to split the ManagerDetails rows with the reference of Link and made query

DECLARE @separator varchar(1) = '/';
WITH rCTE AS(
    SELECT
       1 as [level] ,
       d.data,
       CHARINDEX(@separator, CONCAT(d.data, @separator)) AS Position
    FROM ManagerDetails d   
    UNION ALL
    SELECT
       r.[level]   1,
       r.data,
       CHARINDEX(@separator, CONCAT(r.data, @separator), r.Position   1) AS Position
    FROM rCTE r
    WHERE CHARINDEX(@separator, CONCAT(r.data, @separator), r.Position   1) > 0
)
SELECT [level], SUBSTRING(CONCAT(data, @separator), 1, Position - 1) AS [Value]
FROM rCTE
ORDER BY [level], Position DESC
OPTION (MAXRECURSION 0)

With the above query I'm able to display data column as expected but unable to change the manager and employee columns as I expected.

Appreciate any kind of help.

CodePudding user response：

If I understand the issue correctly, you simply need a LAG() and the positions of the separator in the recursive CTE:

Table:

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

Statement:

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)

Result:

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