Employee Manager Report - Sql Server

I have the below table empid empname managerID 1 A NULL 2 B 1 3 C 1 4 D 2 The ddl is as under Decl

Solution 1:

Declare @t table(empid int, empname varchar(20), managerID int)

insert into @t 
select 1,'A',null union all
select 2,'B',1 union all
select 3,'C',1 union all 
select 4,'D',2

;with CTE AS
(
    Select empid,empname,managerID,
    Cast(empname As Varchar(max)) As ReportingManager
    from @T
    where managerID is null

    UNION ALL

    Select T.empid,T.empname,T.managerID,
    Cast(CTE.empname+'->'+t.empname As Varchar(max)) As ReportingManager
    from @T As T
    INNER JOIN CTE ON T.managerID=CTE.empid 
)
SELECT *
FROM CTE

Solution 2:

I think, your solution is most appropriate, but we can rewrite it, e.g.:

selectt1.empname[EmpName]
    , (select t2.empname from @t t2 where t2.empid = t1.managerID) [ManagerName]from @tt1

Solution 3:

Self join the table

case 1: all employees who have managers

select e1.empname employee, e2.empname manager
from employee e1
join employee e2 on e1.managerId = e2.empid
orderby e2.name,e1.name

case 2: all employees who have managers or not

select e1.empname employee, COALESCE(e2.empname,'none') manager
from employee e1
leftjoin employee e2 on e1.managerId = e2.empid
orderby e2.name,e1.name

Solution 4:

Ok, so you asked for other ways. This is a little freaky.

CREATETABLE employee (empid int, empname varchar(20), managerID int)
GO
insertinto employee 

select1,'A',nullunionallselect2,'B',1unionallselect3,'C',1unionallselect4,'D',2
GO

CREATEFUNCTION [dbo].[GetEmployeeTree](@ManagerIdint)
RETURNS XML
WITHRETURNSNULLONNULL INPUT 
BEGINRETURN 
  (SELECT   empID as'@Id', 
            empname AS'@Name',
            dbo.GetEmployeeTree(empid) 
   FROM employee em
   WHERE ManagerId=@ManagerIdFOR XML PATH('Employee'), TYPE)
END

GO
SELECT  empID as'@Id', 
        empname AS'@Name',
        dbo.GetEmployeeTree(empId) 
FROM employee
WHERE managerId isnullFOR XML PATH('Employee'), ROOT('Employees')

Which gives this output

<Employees><EmployeeId="1"Name="A"><EmployeeId="2"Name="B"><EmployeeId="4"Name="D" /></Employee><EmployeeId="3"Name="C" /></Employee></Employees>

I have actually used this to generate large XML trees with tens of thousands of nodes and it is quite quick. There is probably a way to merge the root query with the child query, I just haven't quite figured that out yet. When I have used this technique in the past I have used a separate link and node table to define the hierarchy and it works a bit cleaner when you do that.