从行变列——MSSQL灵活应用技巧

1. 前言

MS SQL Server 是一款功能强大的关系型数据库管理系统。它支持数据表、视图、存储过程、触发器等多种数据库对象，并可以通过 SQL 语句进行操作，广泛应用于企业数据管理。在 MSSQL 中，灵活应用技巧可以帮助我们更好地利用数据库资源，提高 SQL 语句的效率和速度。

2. 行列转换

2.1 行转列

有时候我们需要把行数据转换为列数据，这时可以使用实用函数 PIVOT 。

PIVOT 的作用：

将一组 SELECT 语句执行的结果行转换成列，即把多行数据转换成一行数据，从而形成一个交叉表（也称为“横向分析表格”或“交叉报表”）。

-- 行转列示例代码

SELECT *
FROM (SELECT name, age FROM student) AS s
PIVOT (MAX(age) FOR name IN ([Tom], [Jack], [Mark])) AS p;

该语句的语法大意为：

SELECT *

FROM (SELECT 【需要行转列的字段】 FROM 【数据来源表】) AS s
PIVOT (统计函数(FOR 【需要行转列的字段】 IN (【展示列1】, 【展示列2】, 【展示列3】))) AS p;

例子说明：

将学生表中的 name 和 age 列转换成列：

-- 学生表

CREATE TABLE student(
   ID INT PRIMARY KEY,
   name VARCHAR(10),
   age INT);
INSERT INTO student VALUES
(1001, 'Tom', 18),
(1002, 'Jack', 20),
(1003, 'Mark', 19);
-- 行转列
SELECT *
FROM (SELECT name, age FROM student) AS s
PIVOT (MAX(age) FOR name IN ([Tom], [Jack], [Mark])) AS p;

输出结果为：

+-----+----+----+-----+

|  ID | Tom |Jack| Mark|
+-----+----+----+-----+
| 1001| 18 |NULL|NULL |
| 1002|NULL| 20 |NULL |
| 1003|NULL|NULL| 19  |
+-----+----+----+-----+

2.2 列转行

相反地，有时候我们需要把列数据转换为行数据，这时可以使用实用函数 UNPIVOT。

UNPIVOT 的作用：

把行表格转换成列表格，即把一行数据转换成多行数据，从而形成一个纵向分析表格，常用于对列进行多个统计的情况。

-- 列转行示例代码

SELECT stu_name, course, score
FROM scores
UNPIVOT(score FOR course IN (C语言, Java, SQL))AS s
WHERE s.score IS NOT NULL;

该语句的语法大意为：

SELECT 【UNPIVOT 的结果需要显示的字段】

FROM 【数据来源表】
UNPIVOT (【需要转换的字段】 FOR 【分类字段】 IN (【展示列1】, 【展示列2】, 【展示列3】)) AS s
WHERE s.【需要转换的字段】 IS NOT NULL;

例子说明：

将学生成绩表中的 C语言、Java 和 SQL 列转换成行：

-- 学生表

CREATE TABLE scores(
   stu_name VARCHAR(10),
   C语言 INT,
   Java INT,
   SQL INT);
INSERT INTO scores VALUES
('Tom', 80, 85, 90),
('Jack', 90, 88, 82),
('Mark', 98, 92, 89);
-- 列转行
SELECT stu_name, course, score
FROM scores
UNPIVOT(score FOR course IN (C语言, Java, SQL))AS s
WHERE s.score IS NOT NULL;

输出结果为：

+---------+--------+-------+

| stu_name| course | score |
+---------+--------+-------+
| Tom     | C语言  | 80    |
| Tom     | Java   | 85    |
| Tom     | SQL    | 90    |
| Jack    | C语言  | 90    |
| Jack    | Java   | 88    |
| Jack    | SQL    | 82    |
| Mark    | C语言  | 98    |
| Mark    | Java   | 92    |
| Mark    | SQL    | 89    |
+---------+--------+-------+

3. 总结

行列转换是 MSSQL 中实用的技巧之一，可以提高数据处理效率与可读性。

在实际使用中，我们可以根据数据需要动态调整行列，将多行数据转换为一行更加直观，或将多列数据展开为多行分析。