CODEWITHSUNDEEP
sqlsql-servert-sql
DenStudent
DenStudent

Reputation: 928

get parents (and their parents,...) from table with self relationship in SQL Server

I have been breaking my head over this, but without any succes... I have following self-reverencing table (Table A):

ID |EmployeeID | ParentID | Level |
1  |11         | null     | A     |
2  |12         | 11       | B     |
3  |13         | 12       | C     |
4  |14         | 12       | C     |

(it's not well build, but we can't change that anymore)

I need to create a view that gives following result:

ID | EmployeeID | Level | LevelA   | LevelB   | LevelC   | LevelD 
1  | 11         | A     | 11       | null     | null     | null   
2  | 12         | B     | 11       | 12       | null     | null   
3  | 13         | C     | 11       | 12       | 13       | null   
4  | 14         | C     | 11       | 12       | 14       | null

ID, EmployeeID and Level come directly from Table A.

Level A - D gives the parent of that EmployeeID and the next parents in hierarchy. If the Level of the Employee is C, you can say it is a C-level employee so his ID is in column LevelC. His Parent is a B-Level employee, so his ID comes in column LevelB. His patent is a A-level employee (which is the highest rank) and his ID comes in column LevelA.

The empty levels just stay null.

Any ideas/suggestions?

Upvotes: 0

Views: 66

Answers (2)

Cassio Veras
Cassio Veras

Reputation: 336

I think you need something like:

SELECT A.ID, A.EMPLOYEEID, A.LEVEL, PT.A, PT.B, PT.C, PT.D
FROM TABLEA A
INNER JOIN 
(
SELECT * FROM 
(SELECT ID, EMPLOYEEID, LEVEL FROM TABLEA) AS SOURCETABLE
PIVOT (
MAX([EMPLOYEEID])
FOR LEVEL IN ([A], [B], [C], [D])) AS PIVOTTABLE
) AS PT
ON A.ID = PT.ID

This code Works as long as you have four columns, but you'll see null values in the columns not matched by the pivot. Hope it helps.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146469

Use Common Table Expression (CTE)

With MyCTE
As (SELECT EmployeeID, ParentID, Level
    FROM tableA
    WHERE ParentID IS NULL
 UNION ALL
    SELECT EmployeeID, ParentID, Level
    FROM tableA a join MyCTE c
       ON c.EmployeeID = a.ParentID
    WHERE a.ParentID IS NOT NULL )
 SELECT * FROM MyCTE

You really don't even need the Level column in the table

With MyCTE
As (SELECT EmployeeID, ParentID, 1 Level
    FROM tableA
    WHERE ParentID IS NULL
 UNION ALL
    SELECT a.EmployeeID, a.ParentID, Level + 1 Level
    FROM tableA a join MyCTE c
       ON c.EmployeeID = a.ParentID
    WHERE a.ParentID IS NOT NULL )
 SELECT * FROM MyCTE

to just add the extra columns, you can use Pivot,

Upvotes: 0

Related Questions