巧用SQL PIVOT实现环境监测数据的“竖横”转换

水环境监测数据存储结构中有一种模式叫做"竖表模式"，即在监测数据表中，某个点位在某个时间点上各监测项目的浓度测定值在物理表中存储在多条记录中，呈竖状分布。监测数据表中包含测点代码、监测时间、监测项目代码、浓度测定值等，所有监测项目的浓度测定值都存储在同一个字段中，加以监测项目代码作为区分字段。
"竖表模式"的最大特点是灵活、具有较好的扩展性。针对新增监测因子，只需要在监测因子编码表进行维护，增加新的监测因子，就能满足数据存储的要求。这种设计模式非常适合自动监测站以及污染源监测数据的存储。但是"竖表模式"也有一些不足之处，比如数据可读性不好，难以表达某个点位某个时间点上的整体数据情况，不符合传统习惯的"横表"数据阅读方式。如何让"竖表"转换成"横表"是很多应用系统中都要遇到的一个问题。
现假设有一张名称为"自动站监测数据日均值表" 的数据表，详细数据如下：
测点代码
年
月
日
监测项目
污染物浓度
P001
2008
2
1
生化需氧量
298
P001
2008
2
1
总磷
0.526
P001
2008
2
1
化学需氧量
414
P001
2008
2
1
氨氮
1.09
P002
2008
2
3
生化需氧量
198
P002
2008
2
3
总磷
0.426
P002
2008
2
3
化学需氧量
314
P002
2008
2
3
总氮
3.71
   
为了能够将数据转换为横表形式，首先，在SQL Server 2005（或2008）数据库中建立存储过程usp_pivot。SQL创建语句如下：
   
CREATE PROC [dbo].[usp_pivot]
　  @schema_name AS sysname　　  = N'dbo',-- 表/视图的架构
　  @object_name AS sysname　　  = NULL, -- 表/视图的名称
　  @on_rows　　 AS sysname　　  = NULL, -- 分组列--可以是以,相隔的多个列
　  @on_cols　　 AS sysname　　  = NULL, -- 旋转列
　  @agg_func　  AS NVARCHAR(12) = N'MAX',-- 聚集函数
　  @agg_col　　 AS sysname　　  = NULL -- 统计列
AS
　  DECLARE
　  @object AS NVARCHAR(600),
　  @sql　　 AS NVARCHAR(MAX),
　  @cols　  AS NVARCHAR(MAX),
　  @newline AS NVARCHAR(2),
　  @msg　　 AS NVARCHAR(500);
   
　  SET @newline = NCHAR(13) + NCHAR(10);
　  SET @object = QUOTENAME(@schema_name) + N'.'