Oracle资格认证:数据记录行转列进行显示
数据库有Score表的数据如下： tnamesubjectscorestu1Chinese80stu1English60stu1Math75stu2Chinese80stu2English85stu3Chinese85stu3Math90 需要行列转换，获取下列视图： tnameChineseEnglishMathstu1806075stu28085stu38590 如何通过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’)
页:
[1]