ms sql 行列转换

假设有张学生成绩表(tb)如下: Name Subject Result 张三 语文　　74 张三 数学　　83 张三 物理　　93 李四 语文　　74 李四 数学　　84 李四 物理　　94 */ ------------------------------------------------------------------------- /* 想变成 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94 张三 74 83 93 在SQL中，行列转换是一种常见的数据操作，尤其在数据分析和报表生成时十分有用。这个案例主要涉及了如何将数据从行格式转换为列格式，针对的是一个学生成绩表，其中包含姓名、科目和成绩三个字段。我们将分别介绍静态SQL和动态SQL两种方法来实现这个转换。 静态SQL是针对已知列名的情况。在这个例子中，我们知道科目只有语文、数学、物理三门。我们可以使用`CASE`语句配合`MAX`函数进行转换： ```sql select name 姓名, max(case subject when '语文' then result else 0 end) 语文, max(case subject when '数学' then result else 0 end) 数学, max(case subject when '物理' then result else 0 end) 物理 from tb group by name ``` 这段SQL会根据姓名分组，然后对每个学生的每门科目取最大值（这里由于结果应该是唯一的，所以最大值即为实际值）。最终得到的结果是一个以姓名为行标识，科目为列标识的表格。 然而，如果科目数量是未知的或者可能会增加，静态SQL就不再适用。这时我们需要使用动态SQL。动态SQL允许我们在运行时构建SQL语句，这样可以适应任何数量的科目： ```sql declare @sql varchar(8000) set @sql = 'select Name as ' + '姓名' select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']' from (select distinct Subject from tb) as a set @sql = @sql + ' from tb group by name' exec(@sql) ``` 这段代码首先创建了一个变量`@sql`并初始化为"select Name as 姓名"，然后遍历所有独特的科目，为每个科目添加一个`CASE`表达式到`@sql`中。执行动态生成的SQL，得到的结果与静态SQL相同，但可以适应任意数量的科目。 此外，我们还可以在此基础上添加计算字段，如平均分和总分。平均分可以通过计算所有科目的成绩之和除以科目数量得到，总分则是各科目成绩之和。例如，可以修改动态SQL如下： ```sql declare @sql varchar(8000) set @sql = 'select Name as ' + '姓名' select @sql = @sql + ', sum(Result) as ' + '总分', @sql = @sql + ', avg(Result) as ' + '平均分' from (select distinct Subject from tb) as a set @sql = @sql + ' from tb group by name' exec(@sql) ``` 这样，我们就可以得到一个包含了姓名、各科目成绩、总分和平均分的完整报告。 SQL中的行列转换是一个强大的工具，它使得数据的呈现方式更加符合分析和展示的需求。无论是静态SQL还是动态SQL，都能根据具体需求灵活调整，帮助我们更好地管理和理解数据。在实际应用中，根据数据的特性选择合适的方法至关重要。