SQL 实现行列转换

今天有个网友有这样的需求，那就看看这个实例吧

 

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
Insert tb
Select '张三','语文',60 union all
Select '张三','数学',70 union all
Select '张三','英语',80 union all
Select '张三','物理',90 union all
Select '李四','语文',65 union all
Select '李四','数学',75 union all
Select '李四','英语',85 union all
Select '李四','物理',95
go

第一种：
select [姓名]=max([姓名]), 数学=sum(case when [课程]='数学' then [分数] else 0 end),物理=sum(case when [课程]='物理' then [分数] else 0 end),英语=sum(case when [课程]='英语' then [分数] else 0 end),语文=sum(case when [课程]='语文' then [分数] else 0 end) from tb group by [姓名]
第二种：
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql+[课程]+'=sum(case when [课程]='''+[课程]+''' then [分数] else 0 end),' from (SELECT DISTINCT [课程] FROM TB) A
set @sql = left(@sql,len(@sql) - 1)
set @sql = 'select [姓名]=max([姓名]), '+@sql+' from tb group by [姓名] '
exec (@sql)

 

create table #TABLE (Area varchar(10), Date varchar(10), Count int)
go
insert into #TABLE (Area, Date, Count)
values ('BeiJing', '2007-01-01',100000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou', '2007-01-01',200000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-02-19',300000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-02-19',400000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-03-21',500000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-03-21',600000)
go
--sql2000
SELECT Area,
 '2007-01-01'=SUM(CASE Date WHEN '2007-01-01'  THEN [Count] END),
 '2007-02-19'=SUM(CASE Date WHEN '2007-02-19'  THEN [Count] END),
 '2007-03-21'=SUM(CASE Date WHEN '2007-03-21'  THEN [Count] END)
FROM #table
GROUP BY Area
go
--sql2005
SELECT *  FROM
#TABLE
PIVOT(SUM([Count]) FOR Date IN (
 [2007-01-01],[2007-02-19],[2007-03-21])) b