CODEWITHSUNDEEP
sql-serversql-server-2012parent-childhierarchypartition-by
Nils
Nils

Reputation: 514

SQL Server : Parent Child Hierarchy

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:

enter image description here

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

Upvotes: 0

Views: 237

Answers (1)

j.v.
j.v.

Reputation: 997

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

Upvotes: 0

Related Questions