Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi I need to query to get all parent order by name with its immediate child with grand child.

Source table
---------------
Id Name ParentId
1 name1 null
2 name2 null
3 name3 1
4 name4 2
5 name5 3
6 name6 3

I need result like :-
Id Name ParentId
1 name1 null
3 name3 1
5 name5 3
6 name6 3
2 name2 null
4 name4 2

What I have tried:

I tried cte recursive but that query giving me all parent first then second level childs and so on. But i need a query to get parents order by name and after each parent, child records should be there and so on.

tried query
-----------
with cte as (
select t.Id, t.Name, t.ParentId, 0 as [Lvl]
from @Table t
where t.ParentId is null
union all
select c.Id,c.Name, t.ParentId, c.Lvl + 1
from @Table t
inner join cte c on c.ParentId = t.Id
where t.ParentId is not null
)
select * from cte c order by c.Id, c.Lvl, c.ParentId;
Posted
Updated 24-Jul-22 23:14pm
v2
Comments
Maciej Los 25-Jul-22 4:10am    
What's you query?
Sunil Kumar Pandab 25-Jul-22 4:40am    
with cte as (
select t.Id, t.Name, t.ParentId, 0 as [Lvl]
from @Table t
where t.ParentId is null
union all
select c.Id,c.Name, t.ParentId, c.Lvl + 1
from @Table t
inner join cte c on c.ParentId = t.Id
where t.ParentId is not null
)
select * from cte c order by c.Id, c.Lvl, c.ParentId;

Try something like:
SQL
WITH cteSorted As
(
    SELECT
        Id,
        Name,
        ParentId,
        CAST(ROW_NUMBER() OVER (ORDER BY Name) As varchar(max)) As RN
    FROM
        @Table
),
cteRecursive As
(
    SELECT
        Id,
        Name,
        ParentId,
        0 As Level,
        '/' + RN + '/' As Path
    FROM
        cteSorted
    WHERE
        ParentId Is NUll
    
    UNION ALL
    
    SELECT
        T.Id,
        T.Name,
        T.ParentId,
        P.Level + 1,
        P.Path + T.RN + '/'
    FROM
        cteRecursive As P
        INNER JOIN cteSorted As T
        ON T.ParentId = P.Id
)
SELECT
    Id,
    Name,
    ParentId,
    Level
FROM
    cteRecursive
ORDER BY
    CAST(Path As hierarchyid)
;
Hierarchical Data (SQL Server) - SQL Server | Microsoft Docs[^]
 
Share this answer
 
v2
Comments
Maciej Los 25-Jul-22 5:16am    
5ed!
Take a look at this example:

SQL
CREATE TABLE Employees
(
  Id int IDENTITY(1,1),
  EmployeeName varchar(30),
  ParentId int
);

INSERT INTO Employees(EmployeeName, ParentId)
VALUES('name1', null),
('name2', null),
('name3', 1),
('name4', 2),
('name5', 3),
('name6', 3);

;WITH CTE AS
(
  SELECT t.Id, t.EmployeeName, t.ParentId, 1 as [Lvl]
  FROM Employees t
  WHERE t.ParentId IS NULL
  UNION ALL
  SELECT t.Id, t.EmployeeName, t.ParentId, c.Lvl + 1
  FROM Employees t
    INNER JOIN CTE c on t.ParentId = c.Id
  WHERE t.ParentId IS NOT NULL
)
SELECT *
FROM 
(
 SELECT *, ROW_NUMBER() OVER(PARTITION BY Lvl ORDER BY Id) AS RN
 FROM CTE
) AS C
ORDER BY c.RN, c.Lvl; 


Result:
Id 	EmployeeName 	ParentId 	Lvl 	RN
1 	name1 		1 	1
3 	name3 	1 	2 	1
5 	name5 	3 	3 	1
2 	name2 		1 	2
4 	name4 	2 	2 	2
6 	name6 	3 	3 	2


SQL Server 2019 | db<>fiddle[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900