Data Flattening With SQL

Question

This one seems so simple but I cannot figure it out.

I have data stored like this...

 ManagerID     EmployeeID     MangerName
 0             0              Debbie
 1             0              Mark
 2             2              Chris
 3             2              Leo
 4             1              Mar
 5             2              Steve
 6             2              Mar

I want this output to look like

EmployeeID    Manager1    Manager2    Manager3     Manager4
0             Debbie      Mark        Null         Null
1             Mar         Null        Null         Null
2             Chris       Leo         Steve        Mar

I know there can only be four entries so four managers. I know I need to use self joins...but i keep getting back sever rows that look like

0   Debbie  Mark   Mark   Debbie
0   Debbie  Debbie Mark   Debbie    etc.

please help

Answer 1

In SQL Server 2005, Oracle 9 and PostgreSQL 8.4 (or above):

WITH    q AS
        (
        SELECT  employeeId, managerId, ROW_NUMBER() OVER (PARTITION BY employeeID ORDER BY managerId) AS rn
        FROM    mytable
        )
SELECT  q1.employeeId, q1.managerName, q2.managerName, q3.managerName, q4.managerName
FROM    q q1
JOIN    q q2
ON      q2.employeeId = q1.employeeId
        AND q2.rn = 2
JOIN    q q3
ON      q3.employeeId = q1.employeeId
        AND q3.rn = 3
JOIN    q q4
ON      q4.employeeId = q1.employeeId
        AND q4.rn = 4
WHERE   q1.rn = 1

In MySQL:

SELECT  employeeId,
        (
        SELECT  managerName
        FROM    mytable mi
        WHERE   mi.employeeId = md.employeeId
        ORDER BY
                mi.employeeId, mi.managerId
        LIMIT 0, 1
        ) AS manager1,
        (
        SELECT  managerName
        FROM    mytable mi
        WHERE   mi.employeeId = md.employeeId
        ORDER BY
                mi.employeeId, mi.managerId
        LIMIT 1, 1
        ) AS manager2,
        (
        SELECT  managerName
        FROM    mytable mi
        WHERE   mi.employeeId = md.employeeId
        ORDER BY
                mi.employeeId, mi.managerId
        LIMIT 2, 1
        ) AS manager3,
        (
        SELECT  managerName
        FROM    mytable mi
        WHERE   mi.employeeId = md.employeeId
        ORDER BY
                mi.employeeId, mi.managerId
        LIMIT 3, 1
        ) AS manager4
FROM    (
        SELECT  DISTINCT employeeId
        FROM    mytable
        ) md

Answer 2

First I take the distinct list of EmployeeID's, then I join against the table for the MIN ManagerID for that Employee ID. After that it's three LEFT joins each looking for the next MIN ManagerID greater than the previous (if they exist..)

select baseE.EmployeeID, 
       m1.ManagerName as Manager1, 
       ISNULL(m2.ManagerName,'') as Manager2, 
       ISNULL(m3.ManagerName,'') as Manager3, 
       ISNULL(m4.ManagerName,'') as Manager4
from 
       (select distinct EmployeeID 
        from EmployeeManagers 
        order by EmployeeID) baseE
join EmployeeManagers m1 
     on baseE.EmployeeID = m1.EmployeeID
        and m1.ManagerID = (select MIN(ManagerID) 
                            from EmployeeManagers 
                            where EmployeeID = baseE.EmployeeID)
left join EmployeeManagers m2 
          on baseE.EmployeeID = m2.EmployeeID
          and m2.ManagerID = (select MIN(ManagerID) 
                              from EmployeeManagers 
                              where EmployeeID = baseE.EmployeeID 
                              and ManagerID > m1.ManagerID)
left join EmployeeManagers m3 
          on baseE.EmployeeID = m3.EmployeeID
          and m3.ManagerID = (select MIN(ManagerID) 
                              from EmployeeManagers 
                              where EmployeeID = baseE.EmployeeID 
                              and m2.ManagerID IS NOT NULL
                              and ManagerID > m2.ManagerID)
left join EmployeeManagers m4 
          on baseE.EmployeeID = m4.EmployeeID
          and m4.ManagerID = (select MIN(ManagerID) 
                              from EmployeeManagers 
                              where EmployeeID = baseE.EmployeeID 
                              and m3.ManagerID IS NOT NULL
                              and ManagerID > m3.ManagerID)