我正在尝试编写查询以选择数据库表中每个月每个药店的总出站使用量。
到目前为止，这是我所能输出的正确数据。但我想消除选择的行数
select pharmacyid, count(*) as usage, month(datecalled) as month
from outboundcalldata
where datepart(year, datecalled) = 2014
group by pharmacyid, YEAR(DateCalled), month(datecalled)
order by pharmacyid, month
输出示例：
pharmacyid|usage| month
-----------------------
2220000006| 10 | 2
2220000006| 11 | 3
2220000006| 900 | 4
2220000006| 30 | 5
2220000007| 34 | 2
2220000007| 300 | 3
2220000007| 145 | 4
相反，我希望每个药房输出1行，每个月输出一列。
;WITH CTE AS
(
select pharmacyid, count(*) as usage, month(datecalled) as [month]
from outboundcalldata
where datepart(year, datecalled) = 2014
group by pharmacyid, YEAR(DateCalled), month(datecalled)
)
SELECT *
FROM CTE C
PIVOT (SUM(usage)
FOR [month]
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
)p
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句