玩转-SQL2005数据库行列转换

CREATE TABLE RowTest(
    [Name] [nvarchar](10) NULL,--名稱
    [Course] [nvarchar](10) NULL,--課程名稱
    [Record] [int] NULL--課程的分數
)

insert into RowTest values ('张三','语文','91')
insert into RowTest values ('张三','数学','92')
insert into RowTest values ('张三','英语','93')
insert into RowTest values ('张三','生物','94')
insert into RowTest values ('张三','物理','95')
insert into RowTest values ('张三','化学','96')

insert into RowTest values ('李四','语文','81')
insert into RowTest values ('李四','数学','82')
insert into RowTest values ('李四','英语','83')
insert into RowTest values ('李四','生物','84')
insert into RowTest values ('李四','物理','85')
insert into RowTest values ('李四','化学','86')

insert into RowTest values ('小生','语文','71')
insert into RowTest values ('小生','数学','72')
insert into RowTest values ('小生','英语','73')
insert into RowTest values ('小生','生物','74')
insert into RowTest values ('小生','物理','75')
insert into RowTest values ('小生','化学','76')

declare @sql nvarchar(max)
set @sql='select Name'
select @sql=@sql+','+'isnull(max( case when Course='''+TCourse.Course+''' then Record end ),0)'+TCourse.Course
 from (select distinct Course from RowTest)TCourse

set @sql=@sql+' from RowTest group by Name order by Name'

print @sql
exec(@sql)

insert into dbo.RowTest values ('小生','生物','110')

insert into dbo.RowTest values ('小生','計算機','110')

create table CoulumTest
(
 Name nvarchar(10),
 语文  int,
 数学 int,
 英语 int

)

insert into CoulumTest values(N'张三',90,91,92)
insert into CoulumTest values(N'李四',80,81,82)

select CT.Name,Col.name 课程,
(case when Col.name=N'语文' then CT.语文  when Col.name=N'数学' then CT.数学
 when Col.name=N'英语' then CT.英语 end ) as 分数 from CoulumTest CT
left join (select name from SysColumns  Where id=Object_Id('CoulumTest')) Col on Col.name>'Name'