Retrieving Employee,Reporting manager and senior reporting manager

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,
I have to retrieve employee, reporting manager and senior reporting manager

SQL

select
		HR2.EmployeeName as Employee_Name,
		HR1.EmployeeName as Reporting_Manager,
		HR3.EmployeeName as Seniour_Reporting_Manager
from 
		HR HR1, HR HR2, HR HR3
where 
		HR1.EmployeeName= HR2.ReportingManagerName
	 and 
                HR1.ReportingManagerName = HR3.EmployeeName
order by 
		HR2.EmployeeName

but it retriving the data as

Employee_Name|Reporting_Manager|Seniour_Reporting_Manager
-------------|-----------------|---------------------------
abc          |def              |ghi
scd          |asd              |qwe

the value is coming correctly

but i need the reporting mamger to come in same column
like

Employee_Name|Reporting_Manager
-------------|------------------
abc          |def              
abc          |ghi

thank you

Posted 19-Nov-13 18:26pm

write2varun

Updated 19-Nov-13 18:43pm

chaau

v2

Add a Solution

2 solutions

Solution 2

This query solved my problem

SQL

declare  @Reporties table (EmployeeName varchar(50), Reporting_Manager varchar(50))

insert @Reporties (EmployeeName,Reporting_Manager)(
    SELECT H.EmployeeName, H.ReportingManagerName
    FROM HR AS H
    UNION ALL
   select HR2.EmployeeName,HR3.EmployeeName
from 
		HR HR1, HR HR2, HR HR3
where 
		HR1.EmployeeName= HR2.ReportingManagerName
	and HR1.ReportingManagerName = HR3.EmployeeName
)
SELECT distinct EmployeeName, Reporting_Manager
FROM @Reporties
ORDER BY EmployeeName

Posted 19-Nov-13 19:15pm

write2varun

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Accepted Answer · 2013-11-19T18:42:00

If I understood you correctly you have a homework assignment related to the recursive queries[^]. If you follow the link you will find the example that uses exactly the same model (employee->manager).

To adapt the query to your example the result will look something like this:

;WITH DirectReports (EmployeeName, EmployeeManager, Level)
AS
(
-- Anchor member definition
    SELECT e.EmployeeName, 0 AS Level
    FROM HR AS e
    WHERE e.ReportingManagerName IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmployeeName, 
        Level + 1
    FROM HR AS e
    INNER JOIN DirectReports AS d
        ON e.ReportingManagerName = e.EmployeeName
)
-- Statement that executes the CTE
SELECT EmployeeName, EmployeeManager, Level
FROM DirectReports
ORDER BY EmployeeName;