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

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