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
Add a comment
|
2 Answers
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
Comments
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.
5 Comments
Dan Getz
and the cross join would be replaced by a regular self-join in most applications (join made on some other field).
Drew
we would need to examine the schema of such. I showed one. OP didn't. Devil's in the details. So I am looking at your tag specialties, @user3580870. Help me here
Drew
I like interesting questions. This is one. Like to avoid an unnecessary pivot table
Dan Getz
in a real application OP probably meant to have gender counts for each category, for example, each state in US. in this case, one would self-join "on" the state field.
Drew
I am all for real world schemas @user3580870 :)