SQL ServerÖеÄÐÐÁе¹Öü¼ÇÉ

ÐÐÁе¹ÖÃÔÚ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
     
Ч¹û: