行列倒置在sql server中是一种很常见的技巧，在做应用系统的时候，经常需要做一些统计功能避免不了使用行列倒置这个技巧，我小小的做了一下总结：
第一种：sql server 2000中使用case进行行列倒置
create table RowCellConvertTest ( grade varchar(50), sex varchar(50), studentCount int ) go insert into RowCellConvertTest select '一年级','男',100 union all select '一年级','女',200 union all select '二年级','男',100 union all select '二年级','女',100 union all select '三年级','男',200 union all select '三年级','女',200 go
select * from RowCellConvertTest go
--利用SQL Server 2000的case将行转换成列 declare @sql varchar(max) set @sql = 'select grade' select @sql=@sql+ ',sum(case when sex = '''+sex+''' then studentCount else '''' end) as '''+sex+'''' from RowCellConvertTest group by sex --select @sql = substring(@sql,2,len(@sql)) --print @sql select @sql = @sql +'from RowCellConvertTest group by grade' exec(@sql) go
drop table RowCellConvertTest go
效果：
第二种:
--利用SQL Server 2005新特性pivot将行转换成列 select grade,男,女 from ( select studentCount, sex, grade from RowCellConvertTest ) p PIVOT ( sum (studentCount) FOR sex IN (男,女) ) AS pvt ORDER BY pvt.grade;
附加：将列转换成行
--将列转换成行 create table CellRowConvertTest ( grade varchar(50), 男 varchar(50), 女 varchar(50) ) go insert into CellRowConvertTest select '一年级',100,200 union all select '二年级',100,100 union all select '三年级',200,200 go
select * from CellRowConvertTest go
select grade,sex,studentCount from ( select grade,男,女 from CellRowConvertTest ) as p unpivot ( studentCount for sex in (男,女) ) as unpvt; go
drop table CellRowConvertTest go
效果:
