Wednesday, March 9, 2011

Query - How to find my friends in my Department?

[Just Copy & Paste]

declare
@EmployeeDepartment table(DeptID int,EmpID int,EmpName varchar(50))

insert into @EmployeeDepartment(DeptID,EmpID,EmpName)
values(10,101,'Manish')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)
values(10,102,'Milan')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(10,103,'Mukesk')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(12,104,'Amit')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(12,105,'Ashok')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(12,106,'Anand')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(13,107,'Suresh')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(13,108,'Sagar')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(14,109,'Pradeep')

select * from @EmployeeDepartment

SELECT DeptID 'MyDeptID',EmpID 'MyEmpID',EmpName as 'MyName',
(
SELECT EmpName + ',' FROM @EmployeeDepartment E2
WHERE E2.DeptID= E1.DeptID
and E1.EmpID <> E2.EmpID
ORDER BY EmpName
FOR XML PATH('')
) AS MyDeptFriend
FROM @EmployeeDepartment E1

No comments:

Post a Comment