SQL行列转换：从行转列和列转行方面详解

一、SQL行转列列转行函数

在SQL语言中，行转列和列转行操作常用的函数有PIVOT和UNPIVOT函数。

1. PIVOT函数

PIVOT函数可以将数据从行转换为列。它使用聚合函数在值上执行转置操作，并将唯一值作为列标题。以下是一个示例：

SELECT *
FROM
(SELECT cust_id, prod_id, quantity
FROM orders)
PIVOT
(SUM(quantity)
FOR prod_id IN ([100], [200], [300], [400], [500])) as p;

上面的查询代码中：从orders表中查询cust_id、prod_id和quantity三列，并使用pivot函数将prod_id列中的值以[100],[200],[300],[400],[500]为列标题转换成列。最后结果如下：

+---------+-----+-----+-----+-----+-----+
| cust_id | 100 | 200 | 300 | 400 | 500 |
+---------+-----+-----+-----+-----+-----+
|    1    |  2  |  4  |  8  |  4  |  3  |
+---------+-----+-----+-----+-----+-----+
|    2    |  5  |  9  |  1  |  0  |  2  |
+---------+-----+-----+-----+-----+-----+

2. UNPIVOT函数

UNPIVOT函数与PIVOT函数相反，将列转换为行。它使用UNION操作符将列转换为行。

SELECT cust_id, prod_id, quantity
FROM
(SELECT *
FROM orders
PIVOT
(SUM(quantity)
FOR prod_id IN ([100], [200], [300], [400], [500])) as p)
UNPIVOT
(quantity
FOR prod_id IN ([100], [200], [300], [400], [500])) as u;

上面的查询代码中：使用PIVOT函数将prod_id列中的值以[100],[200],[300],[400],[500]为列标题转换为列，然后使用UNPIVOT函数将列值转换为行。最后结果如下：

+---------+---------+----------+
| cust_id | prod_id | quantity |
+---------+---------+----------+
|    1    |   100   |    2     |
+---------+---------+----------+
|    1    |   200   |    4     |
+---------+---------+----------+
|    1    |   300   |    8     |
+---------+---------+----------+
|    1    |   400   |    4     |
+---------+---------+----------+
|    1    |   500   |    3     |
+---------+---------+----------+
|    2    |   100   |    5     |
+---------+---------+----------+
|    2    |   200   |    9     |
+---------+---------+----------+
|    2    |   300   |    1     |
+---------+---------+----------+
|    2    |   400   |    0     |
+---------+---------+----------+
|    2    |   500   |    2     |
+---------+---------+----------

二、SQL行列转换

1. SQL行转列

SQL行转列是指将行数据转换为列数据。在SQL Server中可以通过UNPIVOT函数将行转换为列。以下是示例：

SELECT cust_id, q1, q2, q3, q4, q5
FROM (SELECT cust_id, prod_id, quantity
FROM orders) p
PIVOT
(SUM(quantity)
FOR prod_id IN (q1, q2, q3, q4, q5)) as pvt;

上面的查询代码中：从orders表中查询cust_id、prod_id和quantity三列，使用PIVOT函数将prod_id列中的值以q1、q2、q3、q4、q5为列标题转换为列，最后结果如下：

+---------+----+----+----+----+----+
| cust_id | q1 | q2 | q3 | q4 | q5 |
+---------+----+----+----+----+----+
|    1    | 2  | 4  | 8  | 4  | 3  |
+---------+----+----+----+----+----+
|    2    | 5  | 9  | 1  | 0  | 2  |
+---------+----+----+----+----+----

2. SQL列转行

SQL列转行是指将列数据转换为行数据。在SQL Server中可以使用UNPIVOT函数将列转换为行。以下是示例：

SELECT cust_id, prod_id, quantity
FROM
(SELECT *
FROM orders
PIVOT
(SUM(quantity)
FOR prod_id IN ([100], [200], [300], [400], [500])) as p)
UNPIVOT
(quantity
FOR prod_id IN ([100], [200], [300], [400], [500])) as u;

上面的查询代码中：使用PIVOT函数将prod_id列中的值以[100],[200],[300],[400],[500]为列标题转换为列，然后使用UNPIVOT函数将列值转换为行。最后结果如下：

+---------+---------+----------+
| cust_id | prod_id | quantity |
+---------+---------+----------+
|    1    |   100   |    2     |
+---------+---------+----------+
|    1    |   200   |    4     |
+---------+---------+----------+
|    1    |   300   |    8     |
+---------+---------+----------+
|    1    |   400   |    4     |
+---------+---------+----------+
|    1    |   500   |    3     |
+---------+---------+----------+
|    2    |   100   |    5     |
+---------+---------+----------+
|    2    |   200   |    9     |
+---------+---------+----------+
|    2    |   300   |    1     |
+---------+---------+----------+
|    2    |   400   |    0     |
+---------+---------+----------+
|    2    |   500   |    2     |
+---------+---------+----------

三、SQL行转列列转行的详细过程

1. SQL行转列的过程

SQL行转列是指将行数据转换为列数据。具体步骤如下：

Step 1：从数据表中选择需要转换的列。

SELECT cust_id, prod_id, quantity
FROM orders

Step 2：将选择的列作为子查询进行聚合，生成需要转换的数据。

SELECT cust_id, prod_id, SUM(quantity) AS QTY_total
FROM orders
GROUP BY cust_id, prod_id

Step 3：使用PIVOT函数将转换后的数据从行转换为列。

SELECT cust_id, [101], [102], [103], [104], [105]
FROM
(SELECT cust_id, prod_id, SUM(quantity) AS QTY_total
FROM orders
GROUP BY cust_id, prod_id) AS t
PIVOT
(SUM(QTY_total)
FOR prod_id IN ([101], [102], [103], [104], [105])) AS pvt;

2. SQL列转行的过程

SQL列转行是指将列数据转换为行数据。具体步骤如下：

Step 1：从数据表中选择需要转换的列。

SELECT *
FROM
(SELECT cust_id, [101], [102], [103], [104], [105]
FROM orders) AS t

Step 2：使用UNPIVOT函数将列转换为行。

SELECT cust_id, prod_id, quantity
FROM
(SELECT cust_id, [101], [102], [103], [104], [105]
FROM orders) AS t
UNPIVOT
(quantity
FOR prod_id IN ([101], [102], [103], [104], [105])) AS u;

四、SQL行转列三种方式

1. 使用PIVOT函数

使用PIVOT函数将数据从行转换为列，以下是示例代码：

SELECT cust_id, [101], [102], [103], [104], [105]
FROM
(SELECT cust_id, prod_id, quantity
FROM orders) AS t
PIVOT
(SUM(quantity)
FOR prod_id IN ([101], [102], [103], [104], [105])) AS pvt;

2. 使用CASE语句和聚合函数

使用CASE语句和聚合函数将数据从行转换为列，以下是示例代码：

SELECT cust_id,
SUM(CASE WHEN prod_id = '101' THEN quantity ELSE 0 END) AS '101',
SUM(CASE WHEN prod_id = '102' THEN quantity ELSE 0 END) AS '102',
SUM(CASE WHEN prod_id = '103' THEN quantity ELSE 0 END) AS '103',
SUM(CASE WHEN prod_id = '104' THEN quantity ELSE 0 END) AS '104',
SUM(CASE WHEN prod_id = '105' THEN quantity ELSE 0 END) AS '105'
FROM orders
GROUP BY cust_id

3. 使用UNION ALL语句

使用UNION ALL语句将数据从行转换为列，以下是示例代码：

SELECT cust_id, '101' AS prod_id, [101] AS quantity
FROM
(SELECT cust_id, [101]
FROM orders) AS t
UNPIVOT
([101]
FOR prod_id IN ([101])) AS u
UNION ALL
SELECT cust_id, '102' AS prod_id, [102] AS quantity
FROM
(SELECT cust_id, [102]
FROM orders) AS t
UNPIVOT
([102]
FOR prod_id IN ([102])) AS u
UNION ALL
SELECT cust_id, '103' AS prod_id, [103] AS quantity
FROM
(SELECT cust_id, [103]
FROM orders) AS t
UNPIVOT
([103]
FOR prod_id IN ([103])) AS u
UNION ALL
SELECT cust_id, '104' AS prod_id, [104] AS quantity
FROM
(SELECT cust_id, [104]
FROM orders) AS t
UNPIVOT
([104]
FOR prod_id IN ([104])) AS u
UNION ALL
SELECT cust_id, '105' AS prod_id, [105] AS quantity
FROM
(SELECT cust_id, [105]
FROM orders) AS t
UNPIVOT
([105]
FOR prod_id IN ([105])) AS u

其中使用UNION ALL语句将多个行转列的结果合并。