CODEWITHSUNDEEP
sqloracle-database
kabdul
kabdul

Reputation: 29

Convert rows to columns on Oracle 11g

I have a table with two columns (Name and Gender). There are 4 records in my table.

Name  Gender
A       Male
B       Female
C       Male
D       Female

I would like to write a SQL Query to convert the above data to below format.

Male      Female
A           B    
C           D

Upvotes: 1

Views: 146

Answers (3)

user330315
user330315

Reputation:

Another possibility similar to Tim's but only requires a single CTE:

with numbered as (
   select name, gender, row_number() over (partition by gender order by name) as rn
   from the_table
)
select f.name as "Female", m.name as "Male"
from numbered f 
  full outer join numbered m on m.rn = f.rn and m.gender = 'Male'
where f.gender = 'Female';

By using a full outer join this also works if there is a different number of rows per gender.

Online example

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Here is one option, using CTEs:

WITH male AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) rn
    FROM yourTable
    WHERE Gender = 'Male'
),
female AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) rn
    FROM yourTable
    WHERE Gender = 'Female'
)

SELECT m.Name AS Male, f.Name AS Female
FROM male m
INNER JOIN female f
    ON m.rn = f.rn;

Demo

Upvotes: 0

pid
pid

Reputation: 11607

It may be as simple as that:

SELECT m.Name Male, f.Name Female
FROM t m, t f
WHERE m.Gender = "Male" AND f.Gender = "Female";

But it doesn't make much sense, I hope you know what you're doing.

Upvotes: 0

Related Questions