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.
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