0

How can i print the below structure

+--------+------------+
| Male   | Female     |
+--------+------------+
| Salman |   Meena    |
| Govinda|   Kanika   |
+--------+------------+

details from table employee

employeename    sex
---------------------
Salman          male
Meena           female
Kanika          female
Govinda         male

I am using below query but output is not as expected as I need.

Query:

select
    (if(emp1.sex='male',emp1.employeename,'')) as Male,
    (if(emp1.sex='female',emp1.employeename,'')) as Female
from employee emp1;


actual output: 

+--------+------------+
| Male   | Female     |
+--------+------------+
| Salman |          |
|        |   Meena    |
|        |   Kanika   |
| Govinda|            |
+--------+------------+
axiac
  • 68,258
  • 9
  • 99
  • 134
Anup Mahamalla
  • 9
  • 3
  • 2
    You'd have to use a self-join to do this in SQL. However, fields in a row are typically related. Why does Salman appear on the same row as Meena? Why Govinda with Kanika? And remember, a table is an **unordered** set of rows. – Bacon Bits Aug 19 '17 at 18:54
  • Hi @Bacaon Bits It looks like silly for Salman appear on the same row as Meena. but this was the requirement – Anup Mahamalla Aug 19 '17 at 18:57
  • use the case then estructure https://dev.mysql.com/doc/refman/5.7/en/case.html – caryarit ferrer Aug 19 '17 at 19:13
  • How is the relationship between male and female? For example, from your table, there are two possible result sets: 1) (Salman, Meena), (Govinda, Kanika) OR 2) (Salman, Kanika), (Govinda, Meena). So what is the rule describing which male belongs to which female? – Binarus Aug 19 '17 at 19:59
  • Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Norbert Aug 19 '17 at 20:02
  • @Binarus There is no relationship between Salman, meena and Govind, Kanika . Just want to print All male in one column and all females in one column – Anup Mahamalla Aug 20 '17 at 09:54

0 Answers0