Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER Puzzles, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
SELECT Name,
ISNULL((SELECT Dept FROM Complex C0 WHERE C0.Id=tbl1.Manager and grouper=1),”) AS DeptNm,
ISNULL((SELECT Name FROM Complex C0 WHERE C0.Id=tbl1.Manager and grouper=1),”) AS MgrNm
FROM
(
SELECT Id,Name,Dept,Manager,ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS grouper FROM Complex
WHERE Manager>0
)tbl1
LikeLike
;with cte as
(
select a.name name, a.dept dept, b.name manager, ROW_NUMBER() over (partition by b.name order by a.name) as x from Complex a
join complex b
on a.manager=b.id
)
select name, dept=
case
when X=1 then dept else ” end,
manager=
case
when x=1 then manager else ” end
from cte
LikeLike