Grouping or Pivoting data in mysql query to make row as column

Question

I am just facing trouble to sort out the query I need. I have a scenario like this

colDist       colGender           attnedancePrcnt
Place-1        Male                 2.5 %
Place-1        Female               1.5%
Place-2        Male                 3.5%
Place-2        Female               2.1%

Now I need a query in mysql to have the data like this

colDist       colMale           colFemale
Place-1        2.5%                1.5 %
Place-2        3.5%                2.1 %

This is sort of group by coldDist but I am just facing trouble to make the colMale and colFemale from the first data set into the second one.

Advance thanks.

This approach.....

select colDist, if(colGender='Male',colGender,null ) as   colMale ,
        if(colGender='Female',colGender,null ) as   colFemale 
 from tablename

gives me the result like this

colDist       colMale           colFemale          attnedancePrcnt
Place-1        Male                NULL                 2.5
Place-1        NULL                Female               1.5
Place-2        Male                NULL                 3.5
Place-2        NULL                Female               2.1

But again I need them like this:

colDist       colMale           colFemale
Place-1        2.5%                1.5 %
Place-2        3.5%                2.1 %

So how can I group or pivot the data as required?

Thanks.

This is called a pivot table and there are many answers like this on SO: http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when — liquorvicar, Mar 31 '12 at 07:51

score 1 · Answer 1 · 2012-03-31T05:53:16.680

1

Check if it works as you desired:

 select colDist, if(colGender='Male',colGender,null ) as   colMale ,
        if(colGender='Female',colGender,null ) as   colFemale 
 from tablename

edited Mar 31 '12 at 05:53

answered Mar 31 '12 at 05:47

1

Thanks a lot that close to, but I need them based on group by colDist as you have already noticed Place-1 and Place-2. In your solution I found Male colum is null if that is Female and vice a vice. – Shahidul Haque Mar 31 '12 at 06:03
Do you suggest me to use pivot qury, though I have no in depth in pivot query. – Shahidul Haque Mar 31 '12 at 06:18
1

check this : hope it will help you http://stackoverflow.com/questions/1241178/mysql-rows-to-columns – Mar 31 '12 at 07:24

score 1 · Accepted Answer · answered Mar 31 '12 at 06:22

1

SELECT
    t1.colDist,
    t1.attnedancePrcnt AS colMale,
    t2.attnedancePrcnt AS colFemale
FROM
    tablename AS t1
INNER JOIN tablename AS t2 ON t1.colDist = t2.colDist
AND t1.colGender != t2.colGender
WHERE
    t1.colGender = 'Male'

It's also worth noting that "attnedance" should be spelt "attendance".

answered Mar 31 '12 at 06:22

Michael

11,912
6
49
64

No worries. When you've accepted an answer you need to click on the tick next to the answer. It pushes it to the top of the list and when people re-view the question they can easily see which answer was correct. – Michael Mar 31 '12 at 07:55

Grouping or Pivoting data in mysql query to make row as column

2 Answers2