行轉列

-- =============================================================================
-- 標題: 普通行轉列
-- 目的: 愛新覺羅.毓華
-- 原創: takako_mu
-- 时间: 2009-10-19
-- 地点: 昆山
-- =============================================================================

if object_id('tb') is not null drop table tb
create table tb(姓名 nvarchar(10) , 課程 nvarchar(10) , 分數 smallint)
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)
go

-- 靜態sql:課程已知.
select * from tb pivot (sum(分數) for 課程 in (語文,數學,物理)) b

-- 動態sql:課程未知.
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 課程 from tb group by 課程
set @sql = '[' + @sql + ']'
exec ('select * from tb pivot (max(分數) for 課程 in (' + @sql + ')) b')

--------------------------------------------------------------------------------


/**//*在上述結果的基礎上加平均分,總分*/
--靜態sql
select m.*,n.平均分,n.總分 from
(select * from tb pivot (max(分數) for 課程 in (語文,數學,物理)) b) m inner join
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
on m.姓名 = n.姓名

--動態sql
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 課程 from tb group by 課程
set @sql='['+@sql+']'
print @sql
exec ('select m.* , n.平均分 , n.總分 from
(select * from tb 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.姓名')

if object_id('tb') is not null drop table tb