数据库中的行列转换

问题描述 在评教系统中查询教师成绩处涉及到了数据的行转列应用，首先介绍下行转列要解决的问题： 我们数据库中存储的数据结构类似为： 而最后我们要达到的查询效果为： 我们将课程列分成了三列，而对应的分数列的数据作为记录插入到了相应课程的后面。 功能



问题描述

在评教系统中查询教师成绩处涉及到了数据的行转列应用，首先介绍下行转列要解决的问题：

我们数据库中存储的数据结构类似为：

而最后我们要达到的查询效果为：

我们将课程列分成了三列，而对应的分数列的数据作为记录插入到了相应课程的后面。

功能来源

为什么会出现这样的需求呢？

数据库设计的过程中为了满足用户的动态要求（添加字段），可以采用定义字段名表，定义一个字段值的表，以达到用静态表达动态。也就是说以数据库表纵向的增加替代原有的横向延伸，即记录条数的增加替代字段增加。

这样的设计带来了灵活，但同时带来了统计分析的麻烦，因为统计分析时有可能需要显示字段展开的情况。如评教系统中的具体实现：

由于考核项目会进行动态的添加删除，因此设置的考核项目表，存储对教师的考核项目

而存储教师评教分数的表需要获取考核项目数据，存储图示为：

最终需要的显示效果是要将考核项目列为标题，而分值作为记录添加到对应的考核项目下。

此处我们我们通过将动态变化的考核项目作为新的表的记录来存储带来了设计的灵活性，而显示的时候却要进行行列的转换才能得到想要的结果。

具体实现

查找相关资料进行实现，明白了行转列又分成了静态和动态之分，静态的是不需要扩展的，很容易实现，而针对评教系统中的功能则是对应的动态实现。

针对第一个实例

1.通过执行字符串的拼接实现动态行转列

--变量按sql语言顺序赋值

declare @sql varchar(500)

set @sql = 'select 姓名'

select @sql = @sql+',max(case 课程 when '''+ 课程 +''' then 分数 else 0 end)['+课程+']'

from(select distinct 课程 from tb)a	--同from tb group by课程，默认按课程名排序

set @sql= @sql + ' from tb group by 姓名'

exec(@sql)

2.使用isnull、pivot函数

declare @sql varchar(8000)

--获得课程集合

select @sql=isnull(@sql+',','')+ 课程 from tb group by 课程           

set @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+')) a'

exec(@sql)

3.添加算总分、平均分的要求

declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+ 课程 from tb group by 课程

set @sql='select m.* , n.总分,n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in ('+

 @sql+')) b) m ,

(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

exec(@sql)

新得小结

虽然评教系统中已经实现了对行列数据的转换，但想要使用另外一种方法进行实现。进行了对行转列功能的搜索实现之后，在转接到评教系统上的应用上来还是碰到了很多问题，到现在仍没有完全解决，只得暂时先放一放了。对数据库的灵活设计也有了一定的体会，在考虑灵活性的同时也要考虑用户的体验度的。对行列互相转换的还是需要更多的思考呀。