| tname | subject | score |
| stu1 | Chinese | 80 |
| stu1 | English | 60 |
| stu1 | Math | 75 |
| stu2 | Chinese | 80 |
| stu2 | English | 85 |
| stu3 | Chinese | 85 |
| stu3 | Math | 90 |
需要行列转换，获取下列视图：
| tname | Chinese | English | Math |
| stu1 | 80 | 60 | 75 |
| stu2 | 80 | 85 | |
| stu3 | 85 | 90 |
如何通过SQL代码实现呢？ 下面针对SQL 2000/2005 分别给出了方法：
--SQL2000静态sql
SELECT tname,
Chinese = MAX(CASE subject WHEN ’Chinese’ THEN score ELSE 0 END),
English = MAX(CASE subject WHEN ’English’ THEN score ELSE 0 END),
Math = MAX(CASE subject WHEN ’Math’ THEN score ELSE 0 END)
FROM teammember
GROUP BY tname
ORDER BY tname
--SQL2000动态sql
DECLARE @sql NVARCHAR(4000)
SET @sql = ’SELECT tname’
SELECT @sql = @sql + ’, ’ + a.subject + ’ = MAX(CASE subject WHEN ’’’ + a.subject + ’’’ THEN score ELSE 0 END)’
FROM (SELECT DISTINCT subject FROM teammember) a ORDER BY subject
SET @sql = @sql + ’ FROM teammember GROUP BY tname ORDER BY tname’
EXEC(@sql)
--SQL2005静态sql
SELECT * FROM (SELECT * FROM teammember) a PIVOT (MAX(score) FOR subject IN (Chinese, English, Math)) b
--SQL2005动态sql
DECLARE @sql NVARCHAR(4000)
SELECT @sql = ISNULL(@sql + ’,’ , ’’) + subject FROM teammember GROUP BY subject
EXEC (’SELECT * FROM (SELECT * FROM teammember) a PIVOT (MAX(score) FOR subject IN (’ + @sql + ’)) b’)
3COME考试频道为您精心整理，希望对您有所帮助，更多信息在http://www.reader8.com/exam/
