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