Please help me to solve this complex query

oracle-11g

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.

Best Answer

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;