SQL Server纵表与横表相互转换的方法-猿码集

1. 什么是纵表和横表

在开始详细讨论纵表和横表的相互转换之前，让我们先了解一下什么是纵表和横表。

1.1 纵表

纵表（也称为实体属性值模型）是一种经典的关系型数据库表格结构，每一行对应于一个实体，每一列对应于该实体的属性。

例如，以下是一个存储订单信息的纵表：


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_date DATE,
    total_amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1, 'John Doe', '2021-01-01', 100.00),
(2, 'Mary Smith', '2021-01-02', 200.00),
(3, 'Bob Johnson', '2021-01-03', 150.00);

在这个纵表中，每一行代表一个订单，每一列代表订单的属性。例如，第一行代表订单号为1的订单，订单日期为2021-01-01，总金额为100.00美元。

1.2 横表

横表（也称为属性值对模型）则是以属性为基础的数据存储方式，将实体的不同属性作为不同的字段进行存储。

例如，以下是一个存储订单信息的横表：


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_date DATE,
    total_amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1, 'John Doe', '2021-01-01', 100.00),
(2, 'Mary Smith', '2021-01-02', 200.00),
(3, 'Bob Johnson', '2021-01-03', 150.00);
SELECT *
FROM orders
PIVOT (
  SUM(total_amount)
  FOR order_date IN ([2021-01-01], [2021-01-02], [2021-01-03])
) AS p;

在这个横表中，每一行代表一个属性，每一列代表属性值。例如，第一行代表订单号为1的订单，客户名称为John Doe，该订单的总金额为100.00美元。

2. 将纵表转换为横表

如果我们需要将一个纵表转换为横表，我们可以使用SQL Server中的PIVOT运算符。

2.1 PIVOT运算符介绍

PIVOT运算符是Microsoft SQL Server数据管理系统中使用的一个表运算符。使用 PIVOT 运算符将不同的属性值转换成列， PIVOT 之后的语句可以使 SQL 的 SELECT 结构更加自由灵活。

2.2 示例：将纵表转换为横表

以下示例将展示如何将订单信息的纵表转换为横表：


SELECT *
FROM orders
PIVOT (
  SUM(total_amount)
  FOR order_date IN ([2021-01-01], [2021-01-02], [2021-01-03])
) AS p;

其中，PIVOT运算符后紧跟着对总金额列进行SUM操作，FOR子句指定需要转换成列的列名，IN子句用于指定需要转换成列的列名对应的值。

3. 将横表转换为纵表

如果我们需要将一个横表转换为纵表，我们可以使用SQL Server中的UNPIVOT运算符。

3.1 UNPIVOT运算符介绍

UNPIVOT运算符是Microsoft SQL Server数据管理系统中使用的表运算符之一。它是PIVOT运算符的逆运算符（或称反转运算符），用于将表格的列转换成表格的行。

3.2 示例：将横表转换为纵表

以下示例将展示如何将订单信息的横表转换为纵表：


SELECT order_id, order_date, total_amount
FROM orders
UNPIVOT (
  total_amount
  FOR order_date IN ([2021-01-01], [2021-01-02], [2021-01-03])
) AS u;

其中，UNPIVOT运算符后紧跟着需要转换成行的列名，FOR子句用于指定需要转换成行的列名对应的值。

4. 总结

纵表和横表各有优劣，它们的各自的适用场景也不同。在实际工作中，我们可能需要将一个纵表转换为横表，或者将一个横表转换为纵表。通过使用SQL Server中的PIVOT和UNPIVOT运算符，我们可以轻松地实现这一目的。

SQL Server纵表与横表相互转换的方法