行列轉(zhuǎn)換
要用到SQL SERVER
CASE [字段名] WHEN ‘字段名’ THEN [字段的數(shù)據(jù)值] ELSE 0 END
CASE WHEN [字段名] = ‘字段名’ THEN [字段的數(shù)據(jù)值] ELSE 0 END
判斷一個(gè)字段名是否和這個(gè)改字段名相等，如果相等，就得到字段的數(shù)據(jù)值，否則為0.
Student
uid （key)
name
age
1
張三
22
2
李四
22
3
王五
19
score
sid （key)
sname
score
uid
1
數(shù)學(xué)
111
1
2
英語(yǔ)
122
1
3
語(yǔ)文
119
1
4
數(shù)學(xué)
121
2
5
英語(yǔ)
122
2
6
語(yǔ)文
123
2
7
數(shù)學(xué)
131
3
8
英語(yǔ)
142
3
9
語(yǔ)文
139
3
輸出表格式
uid
age
name
數(shù)學(xué)
英語(yǔ)
語(yǔ)文
SELECT s3.uid,s3.age,s3.name,
SUM(CASE s3.sname WHEN '英語(yǔ)' THEN scores ELSE 0 END) AS '數(shù)學(xué)',
SUM(CASE s3.sname WHEN '數(shù)學(xué)' THEN scores ELSE 0 END) AS '英語(yǔ)',
SUM(CASE s3.sname WHEN '語(yǔ)文' THEN scores ELSE 0 END) AS '語(yǔ)文'
FROM (select s1.uid,s1.name,s1.age,sname,scores,sid from
dbo.student as s1,dbo.score as s2 where s1.uid = s2.uid) as s3
GROUP BY s3.uid,s3.age,s3.name;
