如何从表中选择数据，以转置或交叉表的方式输出？
如何从表中选择数据，以转置或交叉表的方式输出？
提问于 2017-07-24 18:39:25
我有一个表reportdata，值如下：
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我想要如下输出：
reportdata January February May
Assigned user 2 588 22
FHD 0 571 15
ICM 0 0 1
sortingCompleted 0 1 2我尝试过以下查询：
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请建议获得预期输出的方法。谢谢就是预支。
Stack Overflow用户
回答已采纳
发布于 2017-07-24 18:45:05
您可以尝试以下操作：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45278428
复制相关文章
相似问题
腾讯云开发者
