数据记录行转列进行显示
数据库有Score表的数据如下: 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 20xx/20xx 分别给出了方法:
--SQL20xx静态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
--SQL20xx动态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)
--SQL20xx静态sql
SELECT * FROM (SELECT * FROM teammember) a PIVOT (MAX(score) FOR subject IN (Chinese, English, Math)) b
--SQL20xx动态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’)
,数据记录行转列进行显示
- 数据记录行转列进行显示
- › 数据记录行转列进行显示
- 在百度中搜索相关文章:数据记录行转列进行显示
- 在谷歌中搜索相关文章:数据记录行转列进行显示
- 在soso中搜索相关文章:数据记录行转列进行显示
- 在搜狗中搜索相关文章:数据记录行转列进行显示
tag: ORACLE/CIW认证,ciw认证,ciw认证考试,oracle认证,考试频道 - 计算机考试 - ORACLE/CIW认证