http://blog.soojoo.cn/blog/blog.php?do=showone&type=blog&cid=1&itemid=41
问题:假设有张学生成绩表(tb)如下: DHWKD |a
姓名 课程 分数 i)G^UxS|
张三 语文 74 n{P|vH`(
张三 数学 83 -mx `oAT
张三 物理 93 u<H F$g
李四 语文 74 vaoD6cd
李四 数学 84 Of4*'
李四 物理 94 7{w7kY7i
想变成(得到如下结果): [Z-XNT0
姓名 语文 数学 物理 ';'HuJ y
---- ---- ---- ---- +|{l2lu
李四 74 84 94 +s'8^qz
张三 74 83 93 9cS0
------------------- 2<a2N{LP~
*/ wlf< nzS{
{?m"2Pr
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int) sq +j
insert into tb values('张三' , '语文' , 74) xTT1FS]0
insert into tb values('张三' , '数学' , 83) ly}vT;l{
insert into tb values('张三' , '物理' , 93) T*!P<Tw
insert into tb values('李四' , '语文' , 74) - # ZU|3#N
insert into tb values('李四' , '数学' , 84) +YC+CNp
insert into tb values('李四' , '物理' , 94) }_~a0N,/
go HPujAK([
N{J{wx>
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同) .07P xL
select 姓名 as 姓名 , is:y0
max(case 课程 when '语文' then 分数 else 0 end) 语文, aD"t0h;
max(case 课程 when '数学' then 分数 else 0 end) 数学, fJ &UDE s
max(case 课程 when '物理' then 分数 else 0 end) 物理 XO>v
from tb JRx5<, sK
group by 姓名 Aa#**O
3kqmc=d
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) w3:F ^_
declare @sql varchar(8000) d2./G"
set @sql = 'select 姓名 ' rF4+)/{=?
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' m R1f
from (select distinct 课程 from tb) as a 9 KGeT99]h
set @sql = @sql + ' from tb group by 姓名' -uON Q
exec(@sql) ,qMF;`UG
h7Q3+!
--SQL SERVER 2005 静态SQL。 ]T/Ziu[n
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b ]T~ 8C`a
8#A5/EwVyk
--SQL SERVER 2005 动态SQL。 ABia{.8
declare @sql varchar(8000) 5:Y6+hj
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程 &.y_/Y&}
set @sql = '[' + @sql + ']' cdeyUqN
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b') F"6;pe
"pk*f&
--------------------------------- 'p>K:F#(
E!$FfUOT
/* yo( A&!qv<
问题:在上述结果的基础上加平均分,总分,得到如下结果: q;5oyPgnx
姓名 语文 数学 物理 平均分 总分 %#b{ch
---- ---- ---- ---- ------ ---- TeQU`~
李四 74 84 94 84.00 252 %mLJt8h'1f
张三 74 83 93 83.33 250 =~E~kbq
*/ qu0bM|Fela
r3d%|3
--SQL SERVER 2000 静态SQL。 ?2CmbI*L
select 姓名 姓名, HVN/W 8
max(case 课程 when '语文' then 分数 else 0 end) 语文, e7rJ[aX:~
max(case 课程 when '数学' then 分数 else 0 end) 数学, 9F9'hr{"
max(case 课程 when '物理' then 分数 else 0 end) 物理, n RRW<^.+
cast(avg(分数*1.0) as decimal(18,2)) 平均分, W:{0p6S
sum(分数) 总分 TyL-HGN^
from tb K4{//(Js
group by 姓名 nn<xNe-x
/pMU cj.
--SQL SERVER 2000 动态SQL。 vfyzl-24I
declare @sql varchar(8000) ZmJ20,J
set @sql = 'select 姓名 ' p<wh-ZH
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' DW_bUe:J6
from (select distinct 课程 from tb) as a JgWpD
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名' J<^Wa
exec(@sql) )nGFn<@SoO
rh!52Sr;s
--SQL SERVER 2005 静态SQL。 oL{- / ;|
select m.* , n.平均分 , n.总分 from 3>J@q6NL
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m, t6vC fgM[H
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n 1rR=4B
where m.姓名 = n.姓名 YX!:W(?[/T
>'<q*u{+g
--SQL SERVER 2005 动态SQL。 +"%HQ?VJ!5
declare @sql varchar(8000) J2:wA`"/$
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程 a<'{m<
exec ('select m.* , n.平均分 , n.总分 from h[XAR^>{
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , -eSZ Huy
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n N#<vEx}|
where m.姓名 = n.姓名') `Ax?L4bD
2(Z}tlc5
drop table tb }i^fd%)
1*|D.YvL
------------------ 4lH&x"
------------------ g3 v(#t?
#}^'yTx{
/* _~{)e0u5 n
问题:如果上述两表互相换一下:即表结构和数据为: "Uk?e=
姓名 语文 数学 物理 Kvh`QG
张三 74 83 93 OCep /a6z
李四 74 84 94 GPOQ)$LXQ0
想变成(得到如下结果): ) rqpy&
姓名 课程 分数 G|8[YjCR
---- ---- ---- Gs] Mn4g
李四 语文 74 J@m=Y[.>
李四 数学 84 /pIL@6`
李四 物理 94 Yfp<XJO
张三 语文 74 ,<JG:5 !n
张三 数学 83 tMIfa ZT
张三 物理 93 6J},Bt0O
-------------- /0a/IPl
*/ f0)#9*/
_sqchcR
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int) ='t4rx0lA
insert into tb values('张三',74,83,93) dAiNO{.~s
insert into tb values('李四',74,84,94) k%H Zbrq
go @c:E?Ff$i
2B9zL
--SQL SERVER 2000 静态SQL。 <OB?_&]9
select * from mY&s8 '
( H7:[`LTh
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 0VEgc[6R
union all JoRiy/fJ
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb KQFW0VN/
union all %=FgK<b~
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb p,~0?+P s
) t ^X}!M&/d
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end '_g7!Z
uty3=`
--SQL SERVER 2000 动态SQL。 GS^ /k)~
--调用系统表动态生态。 qBuJmE
declare @sql varchar(8000) h*?[sl
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb' +4ih6`H&]
from syscolumns e=taZ LmW
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列 2Jr0^"
order by colid asc YsJ`z!P9
exec(@sql + ' order by 姓名 ') GY;{ JhF
b3n0"
--SQL SERVER 2005 动态SQL。 &>dw}h5'
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t h>p@xmOu
Kq[w3G
--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。 C7}]1mM]
$qr1Al5H
-------------------- !"2hGn6RlA
/* [W+l|
问题:在上述的结果上加个平均分,总分,得到如下结果: O#// ;,w
姓名 课程 分数 Kt7W[!0P
---- ------ ------ SH89,ak:fR
李四 语文 74.00 Z;t/<Bu8
李四 数学 84.00 @4{G/X v_
李四 物理 94.00 5hr"|MzI
李四 平均分 84.00 /%RMM">
李四 总分 252.00 5YP8v;D5O
张三 语文 74.00 ]e=/]:=C
张三 数学 83.00 / l |
张三 物理 93.00 &j Cdo)
张三 平均分 83.33 TQq8xP
张三 总分 250.00 <![G9?
------------------ pzC g<@W.
*/ Z1UZ?tr`
%haIe b1.
select * from }kkD7gwp+
( G=!02y|
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb e(s_^1l
union all B!Y
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb %_ow}&
union all "0NC`WF1>x
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb X,_|c:
union all snAlPf#
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb 3/./o3Cw
union all 2Z= *Y^W7{
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb c3+Qb0k
) t ^Z7Q&xWj
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end %w'|rUXj
mt%{
drop table tb fkv^J jUYJ
]hhxEa}