-3

An interviewer asked me this scenario in a job interview.

We have an Employee table:

GENDER  NAME
------  -----
M       A
F       B
F       C
M       D

How can we write a query to output this:

MALES  FEMALES
-----  -----
A      B
D      C

So we need all the Males in the first column, and all the Females in the second name column. The names can be in any order.

  • 1
    What if you have more female than male employees? Would NULLs be wanted for the extra empty cells? I agree with above comments, this is a horrible question, going against most things you'll need in a real job. – Oreo Feb 15 '18 at 16:30
1

Here is a way to do it on SQL Server. (I don't have an Oracle environment to work on)

;WITH Names (Gender, [Name]) AS (
    SELECT 'M','A' UNION ALL
    SELECT 'F','B' UNION ALL
    SELECT 'F','C' UNION ALL
    SELECT 'M','D'
    )
SELECT M, F
FROM (
    SELECT [Name] M, ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) n
    FROM Names
    WHERE Gender = 'M'
    ) Females
FULL JOIN (
    SELECT [Name] F,ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) n
    FROM Names
    WHERE Gender = 'F'
    ) Males
ON Females.n = Males.n;

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