sql 普通行列转换

在数据的世界里，学生成绩表是一个极其重要的组成部分，它为我们提供了学生的学习成果的直观信息。假设我们有一张学生成绩表，包含了学生的姓名、课程以及对应的分数。现在，我们想要在此基础上进行行列转换，并增加平均分和总分的信息。这是一个挑战，但使用SQL Server的不同版本可以轻松完成。

对于SQL Server 2000版本，我们采用静态和动态SQL的方法来实现这一需求。假设我们有一个学生成绩表`tb`，包含学生的姓名、课程以及分数。针对此表，我们可以写出如下的SQL查询语句：

在静态SQL中，我们知道课程只有语文、数学和物理这三门。我们可以使用CASE语句为每个课程生成一个列，并计算总分和平均分。SQL语句如下：

```sql

select

姓名,

max(case 课程 when '语文' then 分数 else 0 end) as 语文,

max(case 课程 when '数学' then 分数 else 0 end) as 数学,

max(case 课程 when '物理' then 分数 else 0 end) as 物理,

cast(avg(分数) as decimal(18,2)) as 平均分,

sum(分数) as 总分

from tb

group by 姓名;

```

而对于动态SQL，课程的数量可能会变化。为此，我们需要构建一个动态的SQL语句来适应不同的课程。通过构建字符串并使用`exec`执行动态SQL语句，我们可以得到结果：

```sql

declare @sql varchar(8000)

select @sql = isnull(@sql + '],[' , '') + 课程 + ' as ' + 课程

from tb group by 课程

set @sql = '[' + @sql + ']'

set @sql = 'select 姓名, ' + @sql + ', avg(分数) as 平均分, sum(分数) as 总分 from tb group by 姓名'

exec(@sql)

```

对于SQL Server 2005版本，我们可以使用PIVOT功能来更简洁地实现这一需求。无论是静态还是动态SQL，方法都与上述类似，只是在构建查询语句时使用了PIVOT功能。在这里不再赘述。

假设我们的表结构和数据如下：

```sql

create table tb(姓名 varchar(10), 语文 int, 数学 int, 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

```

```sql

select m., n.平均分, n.总分

from

(select 姓名 from tb) m,

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

where m.姓名 = n.姓名;

```

这个查询首先获取所有学生的姓名，并计算他们的平均分和总分，然后将这些信息与原始表进行匹配，得到每个学生的详细信息。接下来是动态SQL的实现：

```sql

declare @sql varchar(max) -- 使用更大的数据类型来适应更长的SQL语句

select @sql = isnull(@sql + ' ', '') + '[' + 课程 + ']' from (select distinct 课程 from tb) as 课程列表 -- 构建课程列表的拼接字符串

exec ('select 姓名 , 课程 , max(分数) as 分数 from tb unpivot (分数 for 课程 in (' + @sql + ')) as t group by 姓名') -- 执行动态SQL查询，使用unpivot将列转换为行来展示每个学生的各科成绩。注意这里使用了group by来确保每个学生只出现一次，并显示其最高分。

```

学生成绩概览

以下是两位学生，李四和张三，在各科目中的成绩以及平均分和总分。

李四的成绩如下：

语文：74.00分

数学：84.00分

物理：94.00分

李四的平均分为：（74.00 + 84.00 + 94.00）/ 3 = 84.00分

李四的总分为：74.00 + 84.00 + 94.00 = 252.00分

张三的成绩如下：

语文：74.00分

数学：83.00分

物理：93.00分

张三的平分为：（74.00 + 83.00 + 93.00）/ 3 = 83.33分

张三的总分为：74.00 + 83.00 + 93.00 = 250.00分

通过SQL查询，我们可以得到更为详细的数据表，包括每位学生的姓名、课程名称（语文、数学、物理、平均分和总分）、以及对应的分数。数据表按照姓名、课程顺序进行排序。

在这张数据表中，"平均分"列是通过将语文、数学和物理的分数相加，然后除以3计算得出的。"总分"列则是将语文、数学和物理的分数简单相加得到的。这样的数据展示方式，既能看到每位学生在各科目中的表现，又能了解他们的整体水平和在全部科目中的总得分。

（注：以上内容纯属虚构，具体成绩以实际数据为准。）