SQL Server Recursive Self Join

Simplest way to do a recursive self-join?

WITH    q AS 
(
SELECT *
FROM mytable
WHERE ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
UNION ALL
SELECT m.*
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q

By adding the ordering condition, you can preserve the tree order:

WITH    q AS 
(
SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
FROM mytable m
WHERE ParentID IS NULL
UNION ALL
SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY
bc

By changing the ORDER BY condition you can change the ordering of the siblings.

SQL Server recursive self join

Recursive cte to the rescue....

Create and populate sample table (Please save us this step in your future questions):

DECLARE @T as table
(
id int,
name varchar(100),
parent_id int
)

INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)

The cte:

;WITH CTE AS
(
SELECT id, name, name as path, parent_id
FROM @T
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
FROM @T t
INNER JOIN CTE ON t.parent_id = CTE.id
)

The query:

SELECT id, name, path
FROM CTE

Results:

id      name        path
1 A A
5 B B
6 B.1 B,B.1
8 B.2 B,B.2
7 B.1.1 B,B.1,B.1.1
2 A.1 A,A.1
3 A.2 A,A.2
4 A.1.1 A,A.1,A.1.1
9 A.1.1.1 A,A.1,A.1.1,A.1.1.1
10 A.1.1.2 A,A.1,A.1.1,A.1.1.2

See online demo on rextester

Recursive SQL self join

You want a recursive CTE:

with e as (
select cast(name as varchar(max)) as list, empId, 0 as level
from employees
where managerID is null
union all
select e.list + '>' + e2.name, e2.empId, level + 1
from e join
employees e2
on e.empId = e2.managerId
)
select e.*
from e
where not exists (select 1
from employees e2
where e2.managerId = e.empId
);

Sql Server 2014 - Deep Recursive Parent-Child Self Join

You can try to use DISTINCT to filter duplicate rows.

;WITH CTE AS (
SELECT Id, ParentId
FROM T
WHERE ParentId IS NULL
UNION ALL
SELECT t.Id, t.ParentId
FROM T
INNER JOIN CTE c ON t.ParentId = c.Id
)
SELECT DISTINCT Id, ParentId
FROM CTE

How to self JOIN recursively in SQL?

If you are using SQL Server 2005+, you can use common-table expressions

With Family As 
(
Select s.ID, s.ParentSeriesId, 0 as Depth
From Series s
Where ID = @ParentID
Union All
Select s2.ID, s2.ParentSeriesId, Depth + 1
From Series s2
Join Family
On Family.ID = s2.ParentSeriesId
)
Select *
From Family

For more:

Recursive Queries Using Common Table Expressions

Self join or Recursive query to get the count

Here you go. This starts with a simple grouping based on matching products, then uses a loop to recursively group things together until it can find no more merges to do:

CREATE TABLE #data 
(
OrderId INT,
ProductId CHAR(1)
);

INSERT INTO #data (OrderId, ProductId)
VALUES (123,'A'),
(123,'B'),
(223,'B'),
(223,'C'),
(323,'C'),
(323,'D'),
(423,'E');

CREATE TABLE #data2
(
OrderId INT,
ProductId CHAR(1),
Grp INT
);

WITH cte AS
(
SELECT OrderId,
ProductId,
DENSE_RANK() OVER (ORDER BY ProductId) r
FROM #data
)
INSERT INTO #data2(OrderId, ProductId, Grp)
SELECT cte.OrderId,
cte.ProductId,
r
FROM cte

DECLARE @updates INT = 1;

WHILE @updates > 0
BEGIN
-- join groups where there is a lower numbered group that it connects to
UPDATE a
SET Grp = b.Grp
FROM #data2 a
INNER JOIN #data2 b ON b.OrderId = a.OrderId
OR b.ProductId = a.ProductId
WHERE a.Grp > b.Grp

-- end when we have done nothing this cycle
SET @updates = @@ROWCOUNT
END

SELECT
STUFF((SELECT DISTINCT '|'+ ProductId
FROM #data2
WHERE grp = o.grp
FOR xml path('')
), 1, 1, '') AS Products,
COUNT(DISTINCT o.OrderId) AS NumOrders
FROM #data2 o
GROUP BY grp

CTE Recursion statement with self join without ids

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


Related Topics



Leave a reply



Submit