CODEWITHSUNDEEP
sqlsql-serverhierarchical-datarecursive-query
Hemant Bamane
Hemant Bamane

Reputation: 116

Write a query to get hierarchical data in SQL

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

Upvotes: 0

Views: 3734

Answers (4)

Parshuram Kalvikatte
Parshuram Kalvikatte

Reputation: 1646

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]

Upvotes: 0

PowerStar
PowerStar

Reputation: 895

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

Upvotes: 0

urgas9
urgas9

Reputation: 826

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

Upvotes: 0

diiN__________
diiN__________

Reputation: 7666

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

Upvotes: 1

Related Questions