DenStudent DenStudent - 4 months ago 6
SQL Question

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?

Answer

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.