Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008R2

SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.86/5 (49 votes)
15 Sep 2014CPOL3 min read 505.3K   2.2K   64   33
Some queries to find generation of each row, all possible children, all possible parents, managing recursion

Introduction

Here, in this post, we will try to manage data with hierarchical relation or parent-child relation of a specific table in SQL server. Our special concentration would be over.

  • Show Generations of each row
  • Find all possible parents of a specific row
  • Find all possible childs of a specific row
  • Show all possible parents at a column with a separator
  • Show all possible child’s at a column with a separator

Background

Let’s pretend:

  • a row can have only one parent or Null as no parent
  • there is at least a row, without parent (parentId is null)
  • and at least a row, without any child

Here is our table schema:

SQL
/*drop the tbl*/
--DROP TABLE UserType

/*create the tbl*/
CREATE TABLE UserType(
    Id BIGINT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    ParentId BIGINT NULL    
)

Let’s populate the table with data:

SQL
/*insert data into table*/
DECLARE @maxCount BIGINT,
        @count BIGINT,
        @parentId BIGINT;        
SET @maxCount = 10;        /*change to input more*/
SET @count = 1;

WHILE @count <= @maxCount
BEGIN
    If @count = 1
        SET @parentId = NULL;
    ELSE
        SET @parentId = @count - 1;
        
    INSERT INTO UserType(Id, Name, ParentId)
        VALUES (@count, 'User_' + CONVERT(VARCHAR(400), @count), @parentId)
    SET @count = @count + 1;
END

So our populated table would be like:

SQL
/*show inserted datas*/
SELECT *
    FROM UserType;

Check in live http://www.sqlfiddle.com/#!3/f50a6/1

Now how to find all these generations, parents or childs using SQL for a specific row …!!!

The answer is using recursion. But to use this recursion, we need something called CTE (Common Table Expressions) or in syntax “WITH” in SQL. If we don’t have any idea about it, we can start with the links or Google for few moments.

So let’s start with pretty basic stuff.

Regular Join

Joining table with itself based on condition, where ones parentId is equal to another’s Id.

SQL
/*regular join to get detail*/    
SELECT ChildUserType.Id, ChildUserType.Name, ParentUserType.Id, ParentUserType.Name
    FROM UserType AS ChildUserType
    LEFT JOIN UserType AS ParentUserType ON ChildUserType.ParentId = ParentUserType.Id;

Check in live http://www.sqlfiddle.com/#!3/f50a6/2

Row Generation

The procedure is something like:

  • All rows with no parent (NULL), assign generation 0 to them
  • Find rows where parent belongs to the generation 0, and assign increased generation to itself
  • Do until the recursion is finished
SQL
/*row generations*/
WITH Hierarchy(ChildId, ChildName, Generation, ParentId)
AS
(
    SELECT Id, Name, 0, ParentId
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL        
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
    OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/3

All Possible Parents

Check http://stackoverflow.com/a/21233413/2948523

Here, we are trying to find all possible parents of a row where its Id = 5.

  • Starts with selecting the row where Id = 5
  • Find other rows where its id is equal to previously selected ParentId
  • And continue reduction
SQL
--all possible parents of @id
DECLARE @id BIGINT;
SET @id = 5;
WITH tblParent AS
(
    SELECT *
        FROM UserType WHERE Id = @id
    UNION ALL
    SELECT UserType.*
        FROM UserType  JOIN tblParent  ON UserType.Id = tblParent.ParentId
)
SELECT * FROM  tblParent
    WHERE Id <> @id
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/5

All Possible childs

Check http://stackoverflow.com/a/21233413/2948523

Here, we are trying to find all possible childs of a row where its Id = 5.

  • Starts with selecting the row where Id = 5
  • Find other rows where its ParentId is equal to previously selected Id
  • And continue reduction
SQL
-- all possible childs of @userTypeId
DECLARE @userTypeId BIGINT;
SET @userTypeId = 5;
WITH tblChild AS
(
    SELECT *
        FROM UserType WHERE ParentId = @userTypeId
    UNION ALL
    SELECT UserType.* FROM UserType  JOIN tblChild  ON UserType.ParentId = tblChild.Id
)
SELECT *
    FROM tblChild
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/6

All Possible Parents in a Column

Here, we are showing all the possible parent Ids at the column with a specific separator ‘.

SQL
/*row possible parents in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL    
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
    CAST(CASE WHEN Parent.Parents = ''
        THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
        ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/7

All Possible Childs in a Column

Here, we are showing all the possible child's Ids at the column with a specific separator ‘.

SQL
/*row possible childs in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Childs)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS LastGeneration
        WHERE Id NOT IN (SELECT COALESCE(ParentId, 0) FROM UserType)     
    UNION ALL
    SELECT PrevGeneration.Id, PrevGeneration.Name, PrevGeneration.ParentId,
    CAST(CASE WHEN Child.Childs = ''
        THEN(CAST(Child.ChildId AS VARCHAR(MAX)))
        ELSE(Child.Childs + '.' + CAST(Child.ChildId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS PrevGeneration
        INNER JOIN Hierarchy AS Child ON PrevGeneration.Id = Child.ParentId    
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/8

Recursion Limit !!!

In all of the previous queries, we have used syntax like:

SQL
OPTION(MAXRECURSION 32767)

This specifies the maximum number of recursions in CTE. Now if we don’t use this OPTION(MAXRECURSION 32767), by default, it is 100.

We need to specify this number depending on the recursion requirement.

If More Recursion Needed !!!

With MAXRECURSION value equal to 0 means that no limit is applied to the recursion level, but remember a recursion should end at some level.

SQL
OPTION(MAXRECURSION 0)

To increase the recursion number, excluding CTE’s maximum Limit. We can follow some instruction like the links, or Google for some time.

Limitations

Yes, there could be something which I misunderstood or presented. So if you find anything, just let me know.

Find the SQL file as attachment.

License

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


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Mou_kol5-Aug-22 23:02
Mou_kol5-Aug-22 23:02 
GeneralMy vote of 5 Pin
Peter Mobiel20-May-21 0:23
Peter Mobiel20-May-21 0:23 
PraiseNice Article Pin
Paresh B Joshi14-Jan-21 11:17
Paresh B Joshi14-Jan-21 11:17 
QuestionNeed query for one scenario Pin
Member 1483793719-May-20 21:01
Member 1483793719-May-20 21:01 
I have hierarchical data in an employee table in the below format. There are multiple CEOs. Each CEO has a deptHead under him. Every employee including (CEO or deptHead) can be any of the two Types (Say Technical, Functional) which is mentioned in empType column. Each Employee has a manager. The employee table has the below columns:
1. EmpId -Id of employee
2. EmpType - Technical or functional
3. MgrId - Manager of the employee
4. CEOId - To which CEO he comes under.

My requirement is for each employee if he gets any doubt Technically, who is the concerned top most POC above his hierarchy having empType=Technical? It could the CEO or deptHead or his Mgr. The level of hierarchy is not fixed and can go to any level. E1 reports to E2 reports to E3.....reports to En(CEO). FYI, I want to fetch the POC for each employee who is the first employee under a CEO having empType=Technical.

Ex1: CEO = Technical, deptHead=Technical, Mgr=Functional, Emp=Functional (child of CEO is deptHead. Child of deptHead is Mgr, child node of Mgr is Emp). THe naming is given for understanding purpose. There is no column to mention if the EmpId is deptHead or Mgr.
In this example, the Technical POC for all the above employees is CEO.

Ex2: CEO = Functional, deptHead=Technical, Mgr=Functional, Emp=Functional
In this example, the Technical POC for all the above employees is deptHead.
QuestionMultiple Hierarchy in same table Pin
Member 145520346-Aug-19 3:03
Member 145520346-Aug-19 3:03 
QuestionHierarchy How to ? Pin
User 1450779424-Jul-19 20:01
User 1450779424-Jul-19 20:01 
Questioncount all possible childs Pin
drcza14-Aug-18 23:17
drcza14-Aug-18 23:17 
QuestionWhat if the parent child relationships are not defined by an interger Pin
Cameron Stewart18-Jan-18 16:41
Cameron Stewart18-Jan-18 16:41 
QuestionRe: What if the parent child relationships are not defined by an interger Pin
DiponRoy21-Jan-18 5:13
mvaDiponRoy21-Jan-18 5:13 
AnswerRe: What if the parent child relationships are not defined by an interger Pin
Cameron Stewart21-Jan-18 12:37
Cameron Stewart21-Jan-18 12:37 
Questionhow to convert " -- all posible childs of @userTypeId " to LINQ query Pin
Member 2627173(Sina Taghizadeh))23-Nov-17 8:27
Member 2627173(Sina Taghizadeh))23-Nov-17 8:27 
QuestionTHANK YOU SO MUCH Pin
JesseCamron25-May-17 22:22
JesseCamron25-May-17 22:22 
AnswerRe: THANK YOU SO MUCH Pin
DiponRoy29-May-17 6:10
mvaDiponRoy29-May-17 6:10 
GeneralMany Thanks Pin
Boony Developer3-May-17 21:26
Boony Developer3-May-17 21:26 
GeneralRe: Many Thanks Pin
DiponRoy4-May-17 20:49
mvaDiponRoy4-May-17 20:49 
QuestionMillion Thanks Pin
EYO191923-Nov-16 4:31
EYO191923-Nov-16 4:31 
AnswerRe: Million Thanks Pin
DiponRoy23-Nov-16 6:05
mvaDiponRoy23-Nov-16 6:05 
GeneralGreat Article Pin
Alireza_136223-Sep-16 11:02
Alireza_136223-Sep-16 11:02 
GeneralRe: Great Article Pin
DiponRoy25-Sep-16 19:02
mvaDiponRoy25-Sep-16 19:02 
SuggestionI can recommend... (Hierarchies on Steroids) Pin
Paw Jershauge14-Apr-16 20:31
Paw Jershauge14-Apr-16 20:31 
QuestionParent Name Pin
Nagaraju Dasari2-Aug-15 16:46
Nagaraju Dasari2-Aug-15 16:46 
QuestionHow to find the depth of the child efficiently?? Pin
Member 118158015-Jul-15 21:52
Member 118158015-Jul-15 21:52 
AnswerRe: How to find the depth of the child efficiently?? Pin
DiponRoy6-Jul-15 15:54
mvaDiponRoy6-Jul-15 15:54 
Questiongood one Pin
Arkadeep De18-May-15 20:13
professionalArkadeep De18-May-15 20:13 
AnswerRe: good one Pin
DiponRoy18-May-15 21:09
mvaDiponRoy18-May-15 21:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.