sql 行专列 列转行 普通行列转换

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) Zm
J20,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 Jg
WpD  
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  
问题：如果上述两表互相换一下：即表结构和数据为： "U
k?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 %_o
w
}&  
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}