CODEWITHSUNDEEP
mysql
Sameek Mishra
Sameek Mishra

Reputation: 9384

How to get multiple rows into single row with different columns

I'm having the following table structure and the expected output is listed below how to achieve the result :

Gender - Countvalue

Male - 9

Female - 4

Expected output :

Male - Female

9 - 4

Upvotes: 1

Views: 38

Answers (2)

Drew
Drew

Reputation: 24949

create table k
(   gender varchar(20) not null,
    theCount int not null
);
insert k(gender,theCount) values ('male',9),('female',4);


select a.theCount as male, b.theCount as female 
from k a 
cross join k b 
where a.gender='male' and b.gender='female';

+------+--------+
| male | female |
+------+--------+
|    9 |      4 |
+------+--------+

A cross join is a cartesian product. But 1 row by 1 row is 1 row in result set.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try like this:

SELECT  
        max(case when `gender` = 'Male' then countvalue end) as Male,
        max(case when `gender` = 'Female' then countvalue end) as Female
FROM    test

SQL FIDDLE DEMO

Upvotes: 1

Related Questions