Sql 列转行

实现效果

原始数据

原始表

转换后数据

转换后表

示例代码

/****** 删除表******/
IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'[ColumnToRow]') AND OBJECTPROPERTY(ID, 'IsTable') = 1) 
drop table ColumnToRow

/****** 新建测试表，插入测试数据******/
CREATE TABLE ColumnToRow(f_datetime varchar(100), f_so2 int, f_no2 int, f_pm10 int) 
INSERT ColumnToRow SELECT '2014-07-26',18,34,77
INSERT ColumnToRow SELECT '2014-07-27',181,341,771

/****** 查询转换前原始表******/
SELECT * FROM ColumnToRow 

/****** 方法一union ******/
SELECT     f_datetime, 'f_so2' AS 'f_type', f_so2 AS f_value
FROM         ColumnToRow
UNION
SELECT     f_datetime, 'f_no2' AS 'f_type', f_no2 AS f_value
FROM         ColumnToRow
UNION
SELECT     f_datetime, 'f_pm10' AS 'f_type', f_pm10 AS f_value
FROM         ColumnToRow


/****** 方法二UNPIVOT(Sql Server 2005及以上) ******/
SELECT  f_datetime, f_type, f_value
FROM    ColumnToRow UNPIVOT (f_value FOR f_type IN (f_so2, f_no2, f_pm10)) AS pvt