我有一个像下面的表：
Date | Type |
2016/04/01 A
2016/04/01 B
2016/04/02 B
2016/05/07 A
... ...
我尝试进行查询以按月/年分组的每种类型发生次数：
DateByYearMonth | A | B |
04/2016 1 2
05/2016 1 0
...
我尝试了很多事情，但没有想要的东西。我的“基本查询”是：
SELECT
COUNT(1) AS A
FROM DBase.dbo.MyTable
WHERE [Type] = 'A'
AND Date BETWEEN '20140101' AND '20160930'
GROUP BY YEAR(Date),
MONTH(Date)
谁能帮我吗？
因此，您可以使用“按年/月”分组的“分组”上的PIVOT来执行类似的操作。
当然，您必须在PIVOT和主选择中为列添加n个所需数据
select
year,
month,
coalesce(A, 0) as A,
coalesce(B, 0) as B
from
(select
[Type],
Year([Date]) as year,
Month([Date]) as month, count(*) c
from tt1
group by [Type], Year([Date]), Month([Date])
) t
PIVOT(sum(c)
for [Type] in ([A], [B])
) as pvt
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句