### T-SQL经典编程之行转列与列转行技巧详解
#### 一、T-SQL 行转列
在数据库处理中，行转列是一种常见的数据转换需求，特别是当需要将多行记录中的某一列数据按照特定条件聚合到一行中的不同列时。
**实例背景**：基于SQL Server 2005环境下的一个示例，我们将通过具体的步骤展示如何实现这一转换。
**准备工作**：首先创建一个名为`DB`的数据库，并在其中创建两个表——`Scores`和`Student`。
1. **创建Scores表**：该表用于存储学生的各科成绩。
```sql
CREATE TABLE [Scores](
[ID] INT IDENTITY(1,1), -- 自增标识
[StuNo] INT, -- 学号
[Subject] NVARCHAR(30), -- 科目
[Score] FLOAT -- 成绩
);
```
接着插入一些数据作为测试用例。
```sql
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;
```
2. **创建Student表**：此表存储学生基本信息。
```sql
CREATE TABLE [Student](
[ID] INT IDENTITY(100,1), -- 自增标识，学号
[StuName] NVARCHAR(30), -- 姓名
[Sex] NVARCHAR(30), -- 性别
[Age] CHAR(2) -- 年龄
);
```
插入一些学生数据。
```sql
INSERT INTO [Student]
SELECT '张三', '男', 80 UNION
SELECT '李四', '女', 75;
```
**行转列方法**：
- **CASE...WHEN**语句与**GROUP BY...**聚合函数结合使用
- 这种方式通过CASE语句判断科目，利用MAX或SUM函数将同一学号下的不同科目成绩进行聚合。
```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;
```
- **JOIN...ON**连接表实现显示学生姓名
- 通过JOIN将`Scores`表与`Student`表关联，以获取学生的姓名信息。
```sql
SELECT
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;
```
- **PIVOT**实现行转列
- 使用PIVOT操作符可以更简洁地实现行转列的功能。
```sql
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 列转行
列转行是另一种常见的数据变换需求，尤其在需要将某一行中的多个列值转化为多行数据时。
**实例背景**：基于SQL Server 2005环境下的一个示例，我们将通过具体的步骤展示如何实现这一转换。
1. **创建StudentScores表**：用于存储学生的各项成绩。
```sql
CREATE TABLE [StudentScores](
[ID] INT IDENTITY(1,1), -- 自增标识
[StuNo] INT, -- 学号
[Chinese] NVARCHAR(30), -- 语文
[Mathematics] NVARCHAR(30), -- 数学
[English] NVARCHAR(30), -- 英语
[Biology] NVARCHAR(30) -- 生物
);
```
2. **插入测试数据**：
```sql
INSERT INTO [StudentScores]
SELECT 100, 80, 85, 75, 80 UNION
SELECT 101, 90, 80, 70, 75 UNION
SELECT 102, 95, 90, 80, 70 UNION
SELECT 103, 60, 70, 80, 85;
```
**列转行方法**：
- **UNION ALL**方法
- UNION ALL可以用来合并多行数据。例如，我们可以将每个科目成绩分别转换为一行数据，然后使用UNION ALL连接起来。
```sql
SELECT StuNo, '语文' AS Subject, Chinese AS Score
FROM [StudentScores]
UNION ALL
SELECT StuNo, '数学', Mathematics
FROM [StudentScores]
UNION ALL
SELECT StuNo, '英语', English
FROM [StudentScores]
UNION ALL
SELECT StuNo, '生物', Biology
FROM [StudentScores];
```
以上就是T-SQL中行转列和列转行的具体实现方法。这些方法不仅可以帮助我们更好地组织和展示数据，而且对于复杂的数据分析和报告也非常重要。
