SQLServer2005 T-SQL行转列与分页存储过程实战

“行专列、列转行_分页及存储过程” 在SQL语言中，行转列是一种常见的数据操作，它将数据表中的多行数据转换为单行的多个列，反之列转行则是将多列数据转换为多行。这种转换在数据分析和报表展示时非常有用。在T-SQL中，我们可以使用多种方法来实现这种转换，包括使用CASE语句、PIVOT操作以及创建动态SQL等。 首先，我们来看如何在SQL Server 2005环境下进行行转列。这里提供了一个例子，创建了一个名为`Scores`的表，用于存储学生的科目成绩，以及一个`Student`表存储学生的基本信息。`Scores`表包含了学生ID（StuNo）、科目（Subject）和分数（Score），而`Student`表包含学生ID（ID）、姓名（StuName）、性别（Sex）和年龄（Age）。 为了将科目成绩从行转换为列，可以使用CASE...WHEN语句配合GROUP BY子句。在提供的示例中，查询首先按学生ID（StuNo）分组，然后使用CASE语句检查每个学生的每个科目，并返回相应的分数。如果科目不匹配，则返回0。最后，使用MAX函数获取每个科目的最高分，SUM函数计算总分，AVG函数计算平均分。查询结果将显示学号、各科成绩、总分和平均分。 ```sql SELECT StuNo AS '学号', MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学', MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语', MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物', SUM(Score) AS '总分', AVG(Score) AS '平均分' FROM dbo.[Scores] GROUP BY StuNo ORDER BY StuNo ASC ``` 此外，如果需要将列转为行，可以使用PIVOT操作。PIVOT允许你将一组值转换为列，这在处理固定列名的汇总数据时特别有用。例如，如果你想要将“总分”和“平均分”转换为行，可以使用PIVOT。但请注意，PIVOT语法在SQL Server 2005中可能需要手动编写，而在更现代的版本（如SQL Server 2008及以上）中可以使用更简洁的语法。 至于分页，SQL Server提供了OFFSET和FETCH NEXT语句来实现数据的分页查询。假设你想分页显示上述转换后的数据，可以添加以下部分到查询中： ```sql OFFSET (pageNumber - 1) * pageSize ROWS FETCH NEXT pageSize ROWS ONLY ``` 这里，pageNumber是当前页码，pageSize是每页显示的记录数。这样可以获取指定页码的数据。 最后，存储过程是预编译的SQL代码集合，可以在需要的时候多次调用，提高效率。你可以创建一个存储过程来封装上述行转列的查询，以便在需要时方便地调用。例如： ```sql CREATE PROCEDURE GetStudentGrades @pageNumber INT, @pageSize INT AS BEGIN SET NOCOUNT ON; WITH PivotedData AS ( SELECT StuNo AS '学号', MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学', MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语', MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物', SUM(Score) AS '总分', AVG(Score) AS '平均分' FROM dbo.[Scores] GROUP BY StuNo ) SELECT * FROM PivotedData OFFSET (@pageNumber - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY; END ``` 这个存储过程接收两个参数：页码和每页大小，然后返回相应页码的数据。通过这种方式，你可以根据需要灵活地实现数据的分页和行转列处理。