MSSQL实用技巧——行列转换实践

1. 前言

在数据库开发中，我们常常需要对表中的数据进行转换，包括行转列和列转行。本篇文章我们将重点介绍行列转换的实践，为大家提供一些实用技巧和方法。

2. 行列转换概述

行列转换即是将表格中的数据从行转换为列，或者从列转换为行。

例如，下面是一个订单数据表：

CREATE TABLE [Order] (
    [OrderNo] INT PRIMARY KEY,
    [Customer] VARCHAR(50),
    [Product] VARCHAR(50),
    [OrderDate] DATETIME,
    [Amount] DECIMAL(18,2)
)
-- 插入示例数据
INSERT INTO [Order] VALUES (1,'Alice','iPhone', '2020-01-01', 1000)
INSERT INTO [Order] VALUES (2,'Bob','iPad', '2020-01-02', 2000)
INSERT INTO [Order] VALUES (3,'Charlie','MacBook', '2020-01-03', 3000)

现在，我们需要将订单按照商品进行汇总，生成下面的表格：

+----------+----------+---------+
| Product  | OrderNo  | Amount  |
+----------+----------+---------+
| iPhone   | 1        | 1000.00 |
| iPad     | 2        | 2000.00 |
| MacBook  | 3        | 3000.00 |
+----------+----------+---------+

实现这种行列转换的过程，即为本文的重点。

3. 列转行转换方法

3.1 使用UNPIVOT语句

我们可以使用UNPIVOT语句将列转行。

SELECT [Product], [OrderNo], [Amount]
FROM 
(
    SELECT [OrderNo], [iPhone], [iPad], [MacBook]
    FROM 
    (
        SELECT [OrderNo], [Product], [Amount]
        FROM [Order]
    ) AS P
    PIVOT 
    (
        SUM([Amount]) 
        FOR [Product] IN ([iPhone], [iPad], [MacBook])
    ) AS PVT
) AS T
UNPIVOT 
(
    [Amount] FOR [Product] IN ([iPhone], [iPad], [MacBook])
) AS U

以上代码的执行结果与我们需要的结果相同。

3.2 使用CROSS APPLY和VALUES语句

另一种实现列转行的方法是，使用CROSS APPLY和VALUES语句。

SELECT [Product], [OrderNo], [Amount]
FROM [Order]
CROSS APPLY 
(
    VALUES 
        ('iPhone', [iPhone]),
        ('iPad', [iPad]),
        ('MacBook', [MacBook])
) AS T ([Product], [Amount])

结果也与我们需要的结果相同。

4. 行转列转换方法

4.1 使用PIVOT语句

我们可以使用PIVOT语句将行转列。

SELECT [Product], [1] AS [OrderNo_1], [2] AS [OrderNo_2], [3] AS [OrderNo_3]
FROM 
(
    SELECT [OrderNo], [Product], [Amount]
    FROM [Order]
) AS P
PIVOT 
(
    SUM([OrderNo]) 
    FOR [OrderNo] IN ([1], [2], [3])
) AS PVT

以上代码生成的结果如下所示：

+----------+------------+------------+------------+
| Product  | OrderNo_1  | OrderNo_2  | OrderNo_3  |
+----------+------------+------------+------------+
| iPhone   | 1          | NULL       | NULL       |
| iPad     | NULL       | 2          | NULL       |
| MacBook  | NULL       | NULL       | 3          |
+----------+------------+------------+------------+

我们可以看到，我们将数据从行转换为了列。

4.2 使用CASE WHEN语句

另一种行转列的方法是，使用CASE WHEN语句。

SELECT 
    [Product], 
    [OrderNo_1] = MAX(CASE WHEN [OrderNo] = 1 THEN [OrderNo] END),
    [OrderNo_2] = MAX(CASE WHEN [OrderNo] = 2 THEN [OrderNo] END),
    [OrderNo_3] = MAX(CASE WHEN [OrderNo] = 3 THEN [OrderNo] END)
FROM [Order]
GROUP BY [Product]

结果与前面的方法相同。

5. 总结

本文介绍了行列转换的实践，通过示例演示了列转行和行转列的两种方法。希望本文能够对大家在实际工作中遇到的数据转换问题有所帮助。