Hi I have doubt in sql server
Table: Emp
empid | empidref | Name
1 | NULL | a
3 | 1 | b
4 | 2 | c
2 | NULL | d
5 | NULL | e
6 | NULL | f
8 | 7 | g
7 | NULL | h
10 |NULL |aa
I want merge empidref values in empid when empid also same value. if empid values avilable in empidref table then same empidref values consider as empid and corresponding empidref name need retrive if empid values not avilable in empidref table then same empid values consider as empid and corresponding name need retrive
Based on above table I want output like below
Empid | Name
1 | b
2 | c
5 | e
6 | f
7 |g
10 |aa
I tried like below
SELECT e1.empid, e2.Name
FROM Emp e1 INNER JOIN Emp e2
ON e1.empid = e2.empidref
WHERE e1.empidref IS NULL
but is not given expected result
please tell me how to write query to achive this task in sql server
DECLARE @Emp TABLE
([empid] int, [empidref] varchar(4), [Name] varchar(2));
INSERT INTO @Emp
([empid], [empidref], [Name])
VALUES
(1, NULL, 'a'),
(3, '1', 'b'),
(4, '2', 'c'),
(2, NULL, 'd'),
(5, NULL, 'e'),
(6, NULL, 'f'),
(8, '7', 'g'),
(7, NULL, 'h'),
(10, NULL, 'aa');
This is the query:
SELECT
E1.EmpID
,E2.Name
FROM
@Emp AS E1
INNER JOIN @Emp AS E2
ON (E1.empid = E2.EmpIdRef
AND E2.empidref IS NOT NULL)
OR
(E1.empid = E2.EmpId
AND E2.empidref IS NULL
AND NOT EXISTS(SELECT * FROM @Emp AS E3 WHERE E3.EmpIdRef = E1.empid));
Output:
EmpID Name
1 b
2 c
5 e
6 f
7 g
10 aa
Another solution, same output:
;WITH CTE
AS(SELECT EmpID
,EmpIdRef
,Name
,ROW_NUMBER()OVER(PARTITION BY COALESCE(EmpIDRef,EmpID) ORDER BY EmpIDRef DESC, EmpID) AS RN
FROM @Emp)
SELECT
E1.EmpID
,E2.Name
FROM
CTE AS E1
INNER JOIN CTE AS E2
ON E1.EmpID = COALESCE(E2.EmpIDRef,E2.EmpID)
WHERE
E2.RN = 1
Output:
EmpID Name
1 b
2 c
5 e
6 f
7 g
10 aa