Write a query to get hierarchical data in SQL

Question

I am using MS SQL 2008 R2, i want to retrive the data according to employee hierarchy order by oldest to newest data as given in required output section

My Table Data is As Following :

SELECT * FROM 
(
    SELECT Code = 1  ,ParentCode = NULL, EmployeeName  = 'Hemant'
    UNION ALL
    SELECT Code = 2  ,ParentCode = 1, EmployeeName  = 'Sachin'
    UNION ALL
    SELECT Code = 3  ,ParentCode = 2, EmployeeName  = 'Shanti'
    UNION ALL
    SELECT Code = 4  ,ParentCode = 1, EmployeeName  = 'Sandesh'
    UNION ALL
    SELECT Code =  5  ,ParentCode = 3, EmployeeName  = 'Sandeep'
    UNION ALL
    SELECT Code = 6  ,ParentCode = NULL, EmployeeName  = 'Smily'
    UNION ALL
    SELECT Code = 7  ,ParentCode = 6, EmployeeName  = 'Snehi'
    UNION ALL
    SELECT Code =  8  ,ParentCode = 6, EmployeeName  = 'Kiran'
    UNION ALL
    SELECT Code =  9  ,ParentCode = 7, EmployeeName  = 'Shital'
    UNION ALL
    SELECT Code =  10  ,ParentCode = 9, EmployeeName  = 'Simran'
) X

Required OutPut :

Code    Name        ParentCode   Parent
1      Hemant       NULL         NULL
2      Sachin       1            Hemant
3      Shanti       2            Sachin
5      Sandeep      3            shanti
4      Sandesh      1            Hemant
6      Smily        NULL         NULL
7      Snehi        6            Smily
9      Shital       7            Snehi
10     Simran       9            Shital
8      Kiran        6            Smily

diiN__________ · Accepted Answer · 2017-05-09 07:36:10Z

1

You can do it using a LEFT JOIN:

SELECT t1.Code,
       t1.EmployeeName Name,
       t1.ParentCode,
       t2.EmployeeName Parent
FROM yourTable t1
LEFT JOIN yourTable t2 ON t1.ParentCode = t2.Code

answered May 9, 2017 at 7:36

diiN__________

7,7197 gold badges49 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parshuram Kalvikatte · Accepted Answer · 2017-05-09 09:39:25Z

Use Recusrion for this purpose
SELECT * INTO EmployeeDetails FROM 
(
    SELECT Code = 1  ,ParentCode = NULL, EmployeeName  = 'Hemant'
    UNION ALL
    SELECT Code = 2  ,ParentCode = 1, EmployeeName  = 'Sachin'
    UNION ALL
    SELECT Code = 3  ,ParentCode = 2, EmployeeName  = 'Shanti'
    UNION ALL
    SELECT Code = 4  ,ParentCode = 1, EmployeeName  = 'Sandesh'
    UNION ALL
    SELECT Code =  5  ,ParentCode = 3, EmployeeName  = 'Sandeep'
    UNION ALL
    SELECT Code = 6  ,ParentCode = NULL, EmployeeName  = 'Smily'
    UNION ALL
    SELECT Code = 7  ,ParentCode = 6, EmployeeName  = 'Snehi'
    UNION ALL
    SELECT Code =  8  ,ParentCode = 6, EmployeeName  = 'Kiran'
    UNION ALL
    SELECT Code =  9  ,ParentCode = 7, EmployeeName  = 'Shital'
    UNION ALL
    SELECT Code =  10  ,ParentCode = 9, EmployeeName  = 'Simran'
) X

//This is CTE

WITH REC
(Code,ParentValue,EmployeeName,Parent,Level,GRP,LEVELs,[ORDER])
AS
(
SELECT Code,ParentCode ParentValue,EmployeeName,CONVERT(VARCHAR(max),Null) Parent,
1 level ,Code GRP,STR(0) LEVELs,
CAST(ROW_NUMBER()OVER(PARTITION BY ParentCode ORDER BY ParentCode) AS VARCHAR(MAX)) as [ORDER]
FROM EmployeeDetails WHERE ParentCode is null
UNION ALL
SELECT B.Code,b.ParentCode ParentValue,b.EmployeeName,CONVERT(VARCHAR(max),A.EmployeeName) Parent,
 A.level + 1 Level , A.GRP,STR(ROW_NUMBER() OVER(PArtition By B.ParentCode order by B.ParentCode)) LEVELs,
[ORDER] + CAST(ROW_NUMBER()OVER(PARTITION BY B.ParentCode ORDER BY B.ParentCode) AS VARCHAR(MAX)) AS [ORDER]
FROM EmployeeDetails  B 
INNER JOIN REC A ON B.ParentCode = A.Code
)
SELECT*
FROM REC A
ORDER BY A.[ORDER]

urgas9 · Accepted Answer · 2017-05-09 07:38:57Z

I have used temporary table to first store your "select union" data and then queried the data using left join:

SELECT * 
INTO #temp_tbl_test 
FROM (SELECT Code = 1  ,ParentCode = NULL, EmployeeName  = 'Hemant'
    UNION ALL
    SELECT Code = 2  ,ParentCode = 1, EmployeeName  = 'Sachin'
    UNION ALL
    SELECT Code = 3  ,ParentCode = 2, EmployeeName  = 'Shanti'
    UNION ALL
    SELECT Code = 4  ,ParentCode = 1, EmployeeName  = 'Sandesh'
    UNION ALL
    SELECT Code =  5  ,ParentCode = 3, EmployeeName  = 'Sandeep'
    UNION ALL
    SELECT Code = 6  ,ParentCode = NULL, EmployeeName  = 'Smily'
    UNION ALL
    SELECT Code = 7  ,ParentCode = 6, EmployeeName  = 'Snehi'
    UNION ALL
    SELECT Code =  8  ,ParentCode = 6, EmployeeName  = 'Kiran'
    UNION ALL
    SELECT Code =  9  ,ParentCode = 7, EmployeeName  = 'Shital'
    UNION ALL
    SELECT Code =  10  ,ParentCode = 9, EmployeeName  = 'Simran') x

SELECT base.Code, base.EmployeeName, parent.Code AS ParentCode, parent.EmployeeName AS ParentName
FROM #temp_tbl_test base
LEFT JOIN #temp_tbl_test parent 
ON parent.Code = base.ParentCode

PowerStar · Accepted Answer · 2017-05-09 08:38:50Z

0

You can also achive the same output by using UNION ALL and INNER JOIN as below. I have named your table as SampleData

SELECT * FROM 
(SELECT code,EmployeeName as Name,Parentcode,NULL AS EmployeeName
FROM SampleData WHERE Parentcode IS NULL
UNION ALL
SELECT s1.code,s1.EmployeeName as Name,s1.ParentCode,s2.EmployeeName
FROM SampleData s1
JOIN SampleData s2 ON s1.parentcode = s2.Code) X
ORDER BY X.code

answered May 9, 2017 at 8:38

PowerStar

8955 silver badges15 bronze badges

Collectives™ on Stack Overflow

Write a query to get hierarchical data in SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related