SQL2000 行转列

最新推荐文章于 2019-03-20 18:32:00 发布

灬重来灬

最新推荐文章于 2019-03-20 18:32:00 发布

阅读量1.2k

点赞数

分类专栏： MS-SQL SERVER 文章标签： sql object table null

本文链接：https://blog.csdn.net/lin304510260/article/details/7904273

版权

MS-SQL SERVER 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

                        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