SQL Server : Parent Child Hierarchy

Question

I'm having trouble trying to create a parent and child hierarchy.

Query:

SELECT 
    ID,
    [Manager Code],
    Manager,
    CASE WHEN [Manager Code] = 1 then
    LAG(ID) over (order by  [Manager Code] desc, ID, p_id) end as IDParentChild,
    ROW_NUMBER() over (partition by ID order by  [Manager Code] desc, ID, p_id) as RowNum
    FROM Employee
 WHERE ID IN ('90110000000','90110100000','90400000000')

Table:

I hav ID for parent and IDParentChild but how do i not do a parent child relation?.

The nr 1 in Manager code stands for manager and the 0 is the employees.

mssql server 2012

Based on which rule you know that 1504 is parent of 4524, and also that 4524 is manager of 4518? Cleanest way would be to have one additional column "ParentManagerId" that will solve everything. — j.v., Dec 05 '15 at 17:49

score 0 · Answer 1 · answered Dec 05 '15 at 18:18

Try this one:

    SELECT *, 
    CASE WHEN t.ManagerCode = 1 THEN 
        (SELECT max(manager) FROM yourTbl t1 WHERE t1.ID < t.ID AND ManagerCode = 1)
     ELSE 
        (SELECT max(manager) FROM yourTbl t1 WHERE t1.ID = t.ID AND ManagerCode = 1)
     END AS ParentManager
    FROM yourTbl t

SQL Server : Parent Child Hierarchy

1 Answers1