Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Manage and Employee Puzzle – 2 ]

The puzzle is simple. Here we have to find out employees from  the input table with their manager and dept. Please check out the sample input and expected output for details.

Sample Input

Id Name Dept Manager
101 Ranjan SW
102 Deeksha SW 101
103 sham SW 101
104 Junaid SW 101
105 Vinay HQ
106 akhilesh HQ 105
107 Pranav HQ 105

Expected output

Name DeptNm MgrNm
akhilesh HQ Vinay
Pranav
Deeksha SW Ranjan
sham
Junaid

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


----

CREATE TABLE Complex
(Id INT, Name VARCHAR(50), Dept VARCHAR(50), Manager VARCHAR(50))

INSERT INTO Complex VALUES (101,'Ranjan','SW','')
INSERT INTO Complex VALUES (102,'Deeksha','SW','101')
INSERT INTO Complex VALUES (103,'sham','SW','101')
INSERT INTO Complex VALUES (104,'Junaid','SW','101')
INSERT INTO Complex VALUES (105,'Vinay','HQ','')
INSERT INTO Complex VALUES (106,'akhilesh','HQ','105')
INSERT INTO Complex VALUES (107,'Pranav','HQ','105')

---

UPDATE – 20-Apr-2015 – Solution 1


--


SELECT p.Name,CASE WHEN rnk > 1 THEN '' ELSE DeptName END DeptNm, CASE WHEN rnk > 1 THEN '' ELSE MgrName END MgrNm
FROM 
(
      SELECT c.Dept DeptName ,c.Name MgrName , c2.Name , c2.Id  , ROW_NUMBER() OVER (PARTITION BY c.Dept ORDER BY c.Dept ) rnk FROM Complex c
      INNER JOIN Complex c2 ON c.Id = c2.Manager
) P

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com