0

I have a table that contains the following data;

EmpNo | GivenName | PreferredName 
---------------------------------
1     | Paris     | Paris
2     | Ashley    | Ash

I am trying to get an output where, if the GivenName and PreferredName values are same, then the row should be returned as it is, but if they are different then the row should be returned twice (if possible, the row returned twice should have a NULL in PreferredName in the first occurence and GivenName a NULL in the second). Something like this:

EmpNo | GivenName | PreferredName 
---------------------------------
1     | Paris     | Paris
2     | Ashley    | NULL
2     | NULL      | Ash

I have no idea where to begin. I tried using CASE statement and FULL OUTER JOIN to no avail. I have as well tried using the code below, and it is giving me the answer for the first part of my question:

SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp] E
WHERE [GivenName]= [PreferredName]
UNION ALL
SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp] 
  • What happens if GivenName or PreferredName in the table is NULL? – Aaron Bertrand Jul 19 '19 at 2:01
2

You can do this with the below query:

DECLARE @Employees TABLE
(
    EmpNo INT,
    GivenName NVARCHAR(255),
    PreferredName NVARCHAR(255)
)

INSERT INTO  @Employees
VALUES (1, 'Paris', 'Paris'), (2, 'Ashley', 'Ash')

SELECT EmpNo,
    GivenName,
    PreferredName
FROM @Employees
WHERE GivenName = PreferredName
UNION ALL
SELECT EmpNo,
    GivenName,
    NULL AS PreferredName
FROM @Employees
WHERE GivenName <> PreferredName
UNION ALL
SELECT EmpNo,
    NULL AS GivenName,
    PreferredName
FROM @Employees
WHERE GivenName <> PreferredName
| improve this answer | |
1

Different way to skin the cat:

DECLARE @emp table
(
  EmpNo         int,
  GivenName     nvarchar(32),
  PreferredName nvarchar(32)
);

INSERT @emp(EmpNo,GivenName,PreferredName)
VALUES (1,N'Paris',N'Paris'),(2,N'Ashley',N'Ash');

;WITH x AS 
(
  SELECT n.n, e.EmpNo, g = e.GivenName, p = e.PreferredName 
  FROM @emp AS e CROSS JOIN (SELECT 1 UNION ALL SELECT 2) AS n(n)
)
SELECT EmpNo,
  GivenName = CASE WHEN n = 1 THEN g ELSE NULL END,
  PreferredName = CASE WHEN (n = 2) OR (g = p) THEN p ELSE NULL END 
FROM x WHERE n <= CASE WHEN g = p THEN 1 ELSE 2 END
ORDER BY EmpNo, PreferredName;

Results:

enter image description here

DB<>fiddle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.