巧用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'.'
相关文档：
有的时候我们需要一次像数据库中添加多条记录，我们可以使用下面的语句来实现：
--添加一条记录
INSERT INTO tableName(col1,col2,col3) VALUES (1,2,3)
--添加多条记录
INSERT INTO tableName(col1,col2,col3)
SELECT 3,4,5
UNION ALL
SELECT 6,7,8
--从另外的一张表中读取多条数据添加到新表中
INSERT INTO tabl ......
本文从多个角度阐述了SQL Server与存储过程的比较。
①为什么要使用存储过程？
因为它比SQL语句执行快。
②存储过程是什么?
把一堆SQL语句罗在一起,还可以根据条件执行不通SQL语句。
③来一个最简单的存储过程：
CREATE PROCEDURE dbo.testProcedure_AX
AS
select userID from
USERS order by userid ......
看看以下SQL语句：
select row_number() over(partition by xs.xsbh, xs.kch order by coalesce(xs.bkxnxqh, xs.xnxqh) desc) rn
row_number()：代表列
partition by 代表按什么进行分组
order by对每一组信息进行排序
coalesce()是替换的意思 例如：上面的SQL语句的意思是，如果bkxnxqh为空，那么就去xnx ......
xtype 代表类型
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图 ......
其实删除数据库中数据的方法并不复杂，为什么我还要多此一举呢，一是我这里介绍的是删除数据库的所有数据，因为数据之间可能形成相互约束关系，删除操作可能陷入死循环，二是这里使用了微软未正式公开的sp_MSForEachTable存储过程。
也许很多读者朋友都经历过这样的事情：要在开发数据库基础上清理一个空库，但由于对数据� ......
