T-SQL经典行专列、列转行,分页及存储过程.doc

T-SQL 经典编程 说明：本实例是以 SQL Server 2005 为运行环境的。 准备工作：创建一个名为 DB 的数据库（CREATE DATABASE DB）。 一、T-SQL 行转列 1、创建如下表 CREATE TABLE [Scores] ( [ID] INT IDENTITY(1,1), --自增标识 [StuNo] INT, --学号 [Subject] NVARCHAR(30), --科目 [Score] FLOAT --成绩 ) GO INSERT INTO [Scores] SELECT 100, 语文, 80 UNION SELECT 100, 数学, 75 UNION SELECT 100, 英语, 70 UNION SELECT 100, 生物, 85 UNION SELECT 101, 语文, 80 UNION SELECT 101, 数学, 90 UNION SELECT 101, 英语, 70 UNION SELECT 101, 生物, 85 CREATE TABLE [Student] ( [ID] INT IDENTITY(100,1), --自增标识，学号 [StuName] NVARCHAR(30), --姓名 [Sex] NVARCHAR(30), --性别 [Age] CHAR(2) --年龄 ) GO INSERT INTO [Student] SELECT 张三, 男, 80 UNION SELECT 李四, 女, 75通过CASEWHEN 语句GROUP BY…聚合函数 来实现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 ASCSELECT MAX(StuNo) AS 学号, StuName 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] A join [Student] B on (A.StuNo=B.ID) GROUP BY StuName ORDER BY StuName ASC SELECT StuNo AS 学号, StuName AS 姓名, AVG(语文) AS 语文, AVG(数学) AS 数学, AVG(英语) AS 英语, AVG(生物) AS 生物 FROM [Scores] PIVOT( AVG(Score) FOR Subject IN (语文,数学,英语,生物) ) AS NewScores JOIN [Student] ON (NewScores.StuNo=Student.ID) GROUP BY NewScores.StuNo,StuName ORDER BY StuName ASC 结果如下图： 二、T-SQL列转行 1、创建数据表并插入 4 条数据 CREATE TABLE [StudentScores] ( [ID] INT IDENTITY(1,1), --自增标识 [StuNo] INT, --学号 [Chinese] NVARCHAR(30), --语文 [Mathematics] NVARCHAR(30),--数学