SQL SERVER 行列轉換

go
create table tb(姓名 varchar(10) , 課程 varchar(10) , 分數 int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
insert into tb values('李四' , '英文' , 100)
go
select
 *
from
 tb
go
--SQL SERVER 2000 靜態SQL,指課程只有語文、數學、物理這三門課程。(以下同)
select 姓名 as 姓名 ,
  max(case 課程 when '語文' then 分數 else 0 end) 語文,
  max(case 課程 when '數學' then 分數 else 0 end) 數學,
  max(case 課程 when '物理' then 分數 else 0 end) 物理
from tb
group by 姓名
go
--SQL SERVER 2000 動態SQL,指課程不止語文、數學、物理這三門課程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
 
exec(@sql)
go
--SQL SERVER 2005 靜態SQL。
select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理,英文)) b
 

--SQL SERVER 2005 動態SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程
exec ('select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b')

---------------------------------
go

/*
問題：在上述結果的基礎上加平均分，總分，得到如下結果：
姓名 語文 數學 物理 平均分 總分
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
張三 74   83   93   83.33  250
*/

--SQL SERVER 2000 靜態SQL。
select 姓名 姓名,
  max(case 課程 when '語文' then 分數 else 0 end) 語文,
  max(case 課程 when '數學' then 分數 else 0 end) 數學,
  max(case 課程 when '物理' then 分數 else 0 end) 物理,
  cast(avg(分數*1.0) as decimal(18,2)) 平均分,
  sum(分數) 總分
from tb
group by 姓名
go

--SQL SERVER 2000 動態SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 靜態SQL。
select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b) m,
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 動態SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程
exec ('select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名')
go

/*
問題：如果上述兩表互相換一下：即表結構和資料為：
姓名 語文 數學 物理
張三 74　　83　　93
李四 74　　84　　94
想變成(得到如下結果)：
姓名 課程 分數
---- ---- ----
李四 語文 74
李四 數學 84
李四 物理 94
張三 語文 74
張三 數學 83
張三 物理 93
--------------
*/

create table tb1(姓名 varchar(10) , 語文 int , 數學 int , 物理 int)
insert into tb1 values('張三',74,83,93)
insert into tb1 values('李四',74,84,94)
go
select * from tb1
go
--SQL SERVER 2000 靜態SQL。
select * from
(
select 姓名 as 姓名 , 課程 = '語文' , 分數 = 語文 from tb1
union all
select 姓名 as 姓名 , 課程 = '數學' , 分數 = 數學 from tb1
union all
select 姓名 as 姓名 , 課程 = '物理' , 分數 = 物理 from tb1
union all
select 姓名 as 姓名 , 課程 = '平均分' , 分數 = cast((語文 + 數學 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as 姓名 , 課程 = '總分' , 分數 = 語文 + 數學 + 物理 from tb1
) t
order by 姓名 , case 課程 when '語文' then 1 when '數學' then 2 when '物理' then 3 when '平均分' then 4 when '總分' then 5 end

--SQL SERVER 2000 動態SQL。
--調用系統資料表動態生態。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [課程] = ' + quotename(Name , '''') + ' , [分數] = ' + quotename(Name) + ' from tb1'
from syscolumns
where name! = N'姓名' and ID = object_id('tb1') --表名tb，不包含列名為姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 動態SQL。
select 姓名 , 課程 , 分數 from tb1 unpivot (分數 for 課程 in([語文] , [數學] , [物理])) t

--SQL SERVER 2005 動態SQL，同SQL SERVER 2000 動態SQL。

go

--> 測試資料: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (項目 nvarchar(5),值 int,位置 int)
insert into #T
select '項目1',15,1 union all
select '項目1',34,2 union all
select '項目1',56,3 union all
select '項目1',42,4 union all
select '項目2',56,1 union all
select '項目2',67,2 union all
select '項目2',31,3 union all
select '項目2',89,4 union all
select '項目3',45,1 union all
select '項目3',22,2 union all
select '項目3',8,3 union all
select '項目3',23,4
GO
select * from #T
GO
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when 項目='''+項目+''' then 值 end) ['+項目+']'
from #T group by 項目

set @sql=stuff(@sql,1,1,'')

exec ('select '+@sql+',位置 from #T group by 位置')

GO