请教：sql如何合并多条记录
表 student 和 grade 一个学生信息表 一个成绩表
student 主要用到ID（学号） ， name（姓名）
grade主要用到：grade （成绩） stu_ID （学生学号） subject（科目）
student grade
ID（学号） ， name（姓名） stu_ID （学生学号） grade （成绩） subject（科目）
001 张三 001 90 语文
001 95 数学
001 95 英语
现在想做一个视图：如下
id name yuwen shuxue yingyu
001 张三 90 95 95
怎么做？ 谢谢
------解决思路----------------------
--静态
select s.id,s.name,
max(case when g.subject='语文' then grade end) as yuwen,
max(case when g.subject='数学' then grade end) as shuxue,
max(case when g.subject='英语' then grade end) as yingyu
from student s join grade g on s.id=g.stu_id
group by s.id,s.name
------解决思路----------------------
--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT T1.ID,T1.name'
SELECT @SQL=@SQL+',MAX(CASE WHEN T2.subject='''+CAST(subject AS VARCHAR)+''' THEN T2.grade END)['+CAST(subject AS VARCHAR)+']'
FROM grade GROUP BY subject
SET @SQL=@SQL+'FROM student T1 LEFT JOIN grade T2 ON T1.ID=T2.stu_ID GROUP BY T1.ID,T1.name'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT T1.ID,T1.name
,MAX(CASE WHEN T2.subject='语文' THEN T2.grade END)[yuwen]
,MAX(CASE WHEN T2.subject='数学' THEN T2.grade END)[shuxue]
,MAX(CASE WHEN T2.subject='英语' THEN T2.grade END)[yingyu]
FROM student T1
LEFT JOIN grade T2 ON T1.ID=T2.stu_ID
GROUP BY T1.ID,T1.name
------解决思路----------------------
--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT T1.ID,T1.name'
SELECT @SQL=@SQL+',MAX(CASE WHEN T2.subject='''+CAST(subject AS VARCHAR)+''' THEN T2.grade END)['+CAST(subject AS VARCHAR)+']'
FROM grade GROUP BY subject
SET @SQL=@SQL+',T2.number FROM student T1 LEFT JOIN grade T2 ON T1.ID=T2.stu_ID GROUP BY T1.ID,T1.name,T2.number'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT T1.ID,T1.name
,MAX(CASE WHEN T2.subject='语文' THEN T2.grade END)[yuwen]
,MAX(CASE WHEN T2.subject='数学' THEN T2.grade END)[shuxue]
,MAX(CASE WHEN T2.subject='英语' THEN T2.grade END)[yingyu]
,T2.number
FROM student T1
LEFT JOIN grade T2 ON T1.ID=T2.stu_ID
GROUP BY T1.ID,T1.name,T2.number