行列倒置在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
效果:
