行列转换
要用到SQL SERVER
CASE [字段名] WHEN ‘字段名’ THEN [字段的数据值] ELSE 0 END
CASE WHEN [字段名] = ‘字段名’ THEN [字段的数据值] ELSE 0 END
判断一个字段名是否和这个改字段名相等，如果相等，就得到字段的数据值，否则为0.
Student
| uid （key) |
name |
age |
| 1 |
张三 |
22 |
| 2 |
李四 |
22 |
| 3 |
王五 |
19 |
score
| sid （key) |
sname |
score |
uid |
| 1 |
数学 |
111 |
1 |
| 2 |
英语 |
122 |
1 |
| 3 |
语文 |
119 |
1 |
| 4 |
数学 |
121 |
2 |
| 5 |
英语 |
122 |
2 |
| 6 |
语文 |
123 |
2 |
| 7 |
数学 |
131 |
3 |
| 8 |
英语 |
142 |
3 |
| 9 |
语文 |
139 |
3 |
输出表格式
SELECT s3.uid,s3.age,s3.name,
SUM(CASE s3.sname WHEN '英语' THEN scores ELSE 0 END) AS '数学',
SUM(CASE s3.sname WHEN '数学' THEN scores ELSE 0 END) AS '英语',
SUM(CASE s3.sname WHEN '语文' THEN scores ELSE 0 END) AS '语文'
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;
