IF object_id('tempdb..#a') IS NOT NULL
BEGIN
DROP TABLE #a
END
SELECT AppUnitName ,SenderDept,SenderName,SUM(MsgCount) AS SumCount,DATEPART(yyyy,SenderTime) AS Year,Convert(Varchar(10),DATEPART(MM,SenderTime)) AS Mon INTO #a FROM T_Data_SmsMessage
GROUP BY AppUnitName,SenderDept,SenderName,DATEPART(yyyy,SenderTime),Convert(Varchar(10),DATEPART(MM,SenderTime))
SELECT AppUnitName AS 单位名称 ,SenderDept AS 部门名称,SenderName AS 发送人,
sum(case Mon when '1' then SumCount else 0 end) '1月',
sum(case Mon when '2' then SumCount else 0 end) '2月',
sum(case Mon when '3' then SumCount else 0 end) '3月',
sum(case Mon when '4' then SumCount else 0 end) '4月',
sum(case Mon when '5' then SumCount else 0 end) '5月',
sum(case Mon when '6' then SumCount else 0 end) '6月',
sum(case Mon when '7' then SumCount else 0 end) '7月',
sum(case Mon when '8' then SumCount else 0 end) '8月',
sum(case Mon when '9' then SumCount else 0 end) '9月',
sum(case Mon when '10' then SumCount else 0 end) '10月',
sum(case Mon when '11' then SumCount else 0 end) '11月',
sum(case Mon when '12' then SumCount else 0 end) '12月',
sum(SumCount) 合计
FROM #a
GROUP BY AppUnitName,SenderDept,SenderName
ORDER BY AppUnitName,SenderDept,SenderName