你的位置:首页 > 数据库

[数据库]行转列简单应用


数据库环境:SQL SERVER 2008R2

在SQL群看到这样的需求(截图),和对应的建表语句

下面是建表语句和初始化数据

CREATE TABLE T ( 日期 DATE, 数量 NUMERIC(18, 2) )INSERT INTO TVALUES ( '2015-01-01', 10 ),    ( '2015-01-02', 20 ),    ( '2015-02-01', 20 ),    ( '2015-03-01', 30 ),    ( '2015-04-01', 40 ),    ( '2016-01-01', 50 ),    ( '2016-02-01', 60 )

我简单的解释下这个需求,“月平均”字段之前是行转列的应用,“月平均”是指在该年内,有数据的月份的一个平均值,

“年平均”则是对该年12个月份的汇总的一个平均数。

将上述描述的计算方法简化为公式

月平均=该年有数据的月份总和/有数据的月份数

年平均=该年的数据总和/12

下面贴一下实现的SQL代码

/*比较关键的一步,整理数据,将年月作为一个分组条件求和,保证年月在整理好的数据集里是唯一的,方便后面统计有数据的月份数*/WITH  x0     AS ( SELECT  YEAR(日期) AS tyear ,            MONTH(日期) tmonth ,            SUM(数量) AS data        FROM   dbo.T        GROUP BY YEAR(日期),MONTH(日期)       )  SELECT tyear AS 年,MAX(CASE tmonth WHEN 1 THEN data END) [1],  MAX(CASE tmonth WHEN 2 THEN data END) [2],  MAX(CASE tmonth WHEN 3 THEN data END) [3],  MAX(CASE tmonth WHEN 4 THEN data END) [4],  MAX(CASE tmonth WHEN 5 THEN data END) [5],  MAX(CASE tmonth WHEN 6 THEN data END) [6],  MAX(CASE tmonth WHEN 7 THEN data END) [7],  MAX(CASE tmonth WHEN 8 THEN data END) [8],  MAX(CASE tmonth WHEN 9 THEN data END) [9],  MAX(CASE tmonth WHEN 10 THEN data END) [10],  MAX(CASE tmonth WHEN 11 THEN data END) [11],  MAX(CASE tmonth WHEN 12 THEN data END) [12],  CONVERT(NUMERIC(18,2),SUM(data)/COUNT(*)) AS 月平均,  CONVERT(NUMERIC(18,2),SUM(data)/12) AS 年平均--保留2位小数  FROM  x0 GROUP BY tyear

代码比较好理解,关键步骤是在整理数据阶段,相关说明已在代码里注释,这里不再赘述。

附上结果图


(本文完)