mysqlcaseselect-query

How to select data from table to get output in transpose or crosstab manner?


I have a table reportdata with below values :

 reportdata       memberCount     monthname     year
Assigned user        588           February     2017
Assigned user        2             January      2017
Assigned user        22            May          2017
FHD                  571           February     2017
FHD                  15            May          2017
ICM                  1             May          2017
sortingCompleted     1             February     2017
sortingCompleted     2             May          2017

I want output like :

   reportdata           January   February      May
    Assigned user           2       588          22
       FHD                  0       571          15
       ICM                  0        0            1
   sortingCompleted         0        1            2

I have tried below query :

select d.reportdata,
CASE WHEN d.monthname='January' THEN d.MemberCount END AS January,
CASE WHEN d.monthname='February' THEN d.MemberCount END AS February,  
CASE WHEN d.monthname='May' THEN d.MemberCount END AS May from
(
select reportdata, memberCount,monthname
from cme_report where year='2017'
) as d

Please suggest the way to get expected output. Thank is advance.


Solution

  • You can try the following:

    SELECT
        reportdata,
        SUM(CASE WHEN (monthname = 'January') THEN memberCount ELSE 0 END) AS 'January',
        SUM(CASE WHEN (monthname = 'February') THEN memberCount ELSE 0 END) AS 'February',
        SUM(CASE WHEN (monthname = 'May') THEN memberCount ELSE 0 END) AS 'May', 
    FROM reportdata
    WHERE year = '2017'
    GROUP BY reportdata