实现效果
原始数据
原始表
转换后数据
转换后表
示例代码
/****** 删除表******/
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'[T_Test_RowToColumn]') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
drop table T_Test_RowToColumn
/****** 新建测试表，插入测试数据******/
create table T_Test_RowToColumn(PointName nvarchar(20) ,ItemName nvarchar(20) , Value float )
insert into T_Test_RowToColumn values('点位1' ,'监测项目1', 0.1)
insert into T_Test_RowToColumn values('点位1' ,'监测项目2', 0.2)
insert into T_Test_RowToColumn values('点位1' ,'监测项目3', 0.3)
insert into T_Test_RowToColumn values('点位2' ,'监测项目1', 0.2)
insert into T_Test_RowToColumn values('点位2' ,'监测项目2', 0.4)
insert into T_Test_RowToColumn values('点位2' ,'监测项目3', 0.6)
go
/****** 查询转换前原始表******/
select * from T_Test_RowToColumn
go
/*方法一 关键字case*/
/****** 分析过程使用case方式，添加列******/
select PointName,(case ItemName when '监测项目1' then Value end ) as '监测项目1',
(case ItemName when '监测项目2' then Value end ) as '监测项目2',
(case ItemName when '监测项目3' then Value end ) as '监测项目3'
from T_Test_RowToColumn
go
/****** 静态实现(适合需要列少、固定的形式) ******/
select PointName,max(case ItemName when '监测项目1' then Value end ) as '监测项目1',
max(case ItemName when '监测项目2' then Value end ) as '监测项目2',
max(case ItemName when '监测项目3' then Value end ) as '监测项目3'
from T_Test_RowToColumn group by PointName
go
/****** 动态实现(适合需要列不固定的形式) ******/
declare @sql varchar(8000)
set @sql = 'select PointName '
select @sql = @sql + ' , max(case ItemName when ''' + 监测项目 + ''' then Value end) [' + 监测项目 + ']'
from (select distinct ItemName as 监测项目 from T_Test_RowToColumn) as a
set @sql = @sql + ' from T_Test_RowToColumn group by PointName'
exec(@sql)
go
/*方法二 关键字pivot*/
/****** 静态实现(Sql Server 2005及以上) ******/
select * from (select * from T_Test_RowToColumn) a pivot (max(Value) for ItemName in (监测项目1,监测项目2,监测项目3)) b
go
/****** 动态实现(Sql Server 2005及以上) ******/
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + ItemName from T_Test_RowToColumn group by ItemName
set @sql = '[' + @sql + ']'
exec ('select * from (select * from T_Test_RowToColumn) a pivot (max(Value) for ItemName in (' + @sql + ')) b')
go
