一、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语句将多个行转列的结果合并。
