Retrieving Employee,Reporting manager and senior r

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Retrieving Employee,Reporting manager and senior r

Author

Topic

write2varun
Starting Member

2 Posts

Posted - 2013-11-20 : 00:48:44

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



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

VRN

write2varun
Starting Member

2 Posts

Posted - 2013-11-20 : 01:16:40

This query solved my problem


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

VRN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources