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語句將多個行轉列的結果合併。