数据库有Score表的数据如下: tnamesubjectscorestu1Chinese80stu1English60stu1Math75stu2Chinese80stu2English85stu3Chinese85stu3Math90 需要行列转换,获取下列视图: tnameChineseEnglishMathstu1806075stu28085stu38590 如何通过SQL代码实现呢? 下面针对SQL 2000/2005 分别给出了方法:
+ ?: n) q/ i! o2 ^, t, ?9 y --SQL2000静态sql
K: e7 T" ]# w E& p0 F SELECT tname,
1 N1 w0 b" _" v9 Q R! V4 Y Chinese = MAX(CASE subject WHEN ’Chinese’ THEN score ELSE 0 END),+ [( h0 y% I7 E: U4 a
English = MAX(CASE subject WHEN ’English’ THEN score ELSE 0 END),) c0 Z6 I' E# k# A8 t6 x) r
Math = MAX(CASE subject WHEN ’Math’ THEN score ELSE 0 END)9 I, I3 c* o8 M! ^8 b: K/ s
FROM teammember
( b- m( U! I! B4 |# i' N* N: d3 x GROUP BY tname
, ]6 L+ `0 ~# s9 A& G ORDER BY tname
% g" I6 o! a2 r8 T/ h/ P --SQL2000动态sql! y2 ?2 ]( t* k+ q% k. _" J
DECLARE @sql NVARCHAR(4000)
4 Z* @* t7 \: N$ \, s/ z SET @sql = ’SELECT tname’# p6 r) ]. g/ `- M; R
SELECT @sql = @sql + ’, ’ + a.subject + ’ = MAX(CASE subject WHEN ’’’ + a.subject + ’’’ THEN score ELSE 0 END)’
; k0 o" K2 H, d. E FROM (SELECT DISTINCT subject FROM teammember) a ORDER BY subject
1 r/ E( W# N+ P- Y6 p0 K2 N SET @sql = @sql + ’ FROM teammember GROUP BY tname ORDER BY tname’' r2 j3 S3 e( C0 {! h! a" j/ p
EXEC(@sql)+ h( }. W8 H7 |: E. m
--SQL2005静态sql! i+ X7 ~+ r, P" V- O
SELECT * FROM (SELECT * FROM teammember) a PIVOT (MAX(score) FOR subject IN (Chinese, English, Math)) b1 B" ?8 j a$ N0 _
--SQL2005动态sql$ e) v- a' c$ q
DECLARE @sql NVARCHAR(4000)* M8 V5 x. Z2 s# {
SELECT @sql = ISNULL(@sql + ’,’ , ’’) + subject FROM teammember GROUP BY subject' S4 b& l" E( _5 d
EXEC (’SELECT * FROM (SELECT * FROM teammember) a PIVOT (MAX(score) FOR subject IN (’ + @sql + ’)) b’) |