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
Auto Increment After Delete in MySQL
SQL Statement Is Ignoring Where Parameter
Error 1452: Cannot Add or Update a Child Row: a Foreign Key Constraint Fails
Best Way to Select Random Rows Postgresql
How to Populate a Table With a Range of Dates
Difference Between Numeric, Float and Decimal in SQL Server
Postgresql "Column Does Not Exist" But It Actually Does
Removing Duplicate Rows from Table in Oracle
Select Values That Meet Different Conditions on Different Rows
Error: Tcp Provider: Error Code 0X2746. During the SQL Setup in Linux Through Terminal
How to Select Rows With Max(Column Value), Partition by Another Column in MySQL