能否将单行数据转换为多行多列？附SQL示例与预期输出

当然可以实现，针对你给出的Oracle示例场景，这里提供两种常用解决方案：

方法一：UNION ALL（适合字段较少的场景） #

直接构造对应行数据，逻辑简单直观：

SELECT 1 AS A, 3 AS B FROM dual
UNION ALL
SELECT 2 AS A, 4 AS B FROM dual;

如果数据来自实际表（假设表名为your_table，字段为A1,A2,B1,B2），可以改写为：

SELECT A1 AS A, B1 AS B FROM your_table
UNION ALL
SELECT A2 AS A, B2 AS B FROM your_table;

方法二：UNPIVOT + 分组聚合（适合字段较多的场景） #

当存在多组类似字段（如A1-A5、B1-B5）时，这种方法更灵活，无需逐行写UNION：

WITH unpivoted_data AS (
    SELECT 
        SUBSTR(col_name, 1, 1) AS col_category, -- 提取A/B类别
        SUBSTR(col_name, 2) AS row_group,       -- 提取分组编号1/2
        col_value
    FROM (
        -- 这里替换为你的原始查询或表
        SELECT 1 AS A1, 2 AS A2, 3 AS B1, 4 AS B2 FROM dual
    )
    UNPIVOT (
        col_value FOR col_name IN (A1, A2, B1, B2)
    )
)
SELECT 
    MAX(CASE WHEN col_category = 'A' THEN col_value END) AS A,
    MAX(CASE WHEN col_category = 'B' THEN col_value END) AS B
FROM unpivoted_data
GROUP BY row_group
ORDER BY row_group;

执行上述任意查询后，都会得到你期望的输出：

A  | B
_________
 1  | 3
_________
 2  | 4

内容的提问来源于stack exchange，提问作者ejderuby