实现效果
原始数据
原始表
转换后数据
转换后表
示例代码
/****** 删除表******/
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
