SQL行列转换：如何将同一列字段拆分至同一行？

这是典型的**行转列（Pivot）**需求，核心思路是先给每个用户的测试日期按顺序编号，再通过条件聚合或数据库专用Pivot函数将多行数据合并为单行。以下是主流数据库的具体实现方案：

先明确测试表结构与数据 #

假设你的表名为person_tests，可先执行以下SQL创建测试环境（若已有表可跳过）：

CREATE TABLE person_tests (
    person_id VARCHAR(6),
    person_name VARCHAR(20),
    test_date VARCHAR(10) -- 若为DATE类型可直接替换
);

INSERT INTO person_tests VALUES
('000001', 'person1', '11/12/2024'),
('000001', 'person1', '12/18/2024'),
('000001', 'person1', '01/12/2025'),
('000002', 'person2', '10/01/2024'),
('000002', 'person2', '11/01/2024'),
('000002', 'person2', '12/01/2024'),
('000002', 'person2', '01/01/2025');

1. MySQL（5.7+ / 8.0+） #

利用窗口函数ROW_NUMBER()给日期编号，再通过条件聚合实现转列：

SELECT
    person_id,
    person_name,
    MAX(CASE WHEN rn = 1 THEN test_date END) AS test_date1,
    MAX(CASE WHEN rn = 2 THEN test_date END) AS test_date2,
    MAX(CASE WHEN rn = 3 THEN test_date END) AS test_date3,
    MAX(CASE WHEN rn = 4 THEN test_date END) AS test_date4
FROM (
    SELECT
        person_id,
        person_name,
        test_date,
        -- 按日期升序编号，若为DATE类型可直接用test_date排序
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY STR_TO_DATE(test_date, '%m/%d/%Y')) AS rn
    FROM person_tests
) t
GROUP BY person_id, person_name
ORDER BY person_id;

2. SQL Server #

支持两种实现方式，任选其一即可：

方式1：专用PIVOT函数 #

SELECT
    person_id,
    person_name,
    [1] AS test_date1,
    [2] AS test_date2,
    [3] AS test_date3,
    [4] AS test_date4
FROM (
    SELECT
        person_id,
        person_name,
        test_date,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY CONVERT(DATE, test_date, 101)) AS rn
    FROM person_tests
) t
PIVOT (
    MAX(test_date)
    FOR rn IN ([1], [2], [3], [4])
) p
ORDER BY person_id;

方式2：条件聚合（通用兼容） #

SELECT
    person_id,
    person_name,
    MAX(CASE WHEN rn = 1 THEN test_date END) AS test_date1,
    MAX(CASE WHEN rn = 2 THEN test_date END) AS test_date2,
    MAX(CASE WHEN rn = 3 THEN test_date END) AS test_date3,
    MAX(CASE WHEN rn = 4 THEN test_date END) AS test_date4
FROM (
    SELECT
        person_id,
        person_name,
        test_date,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY CONVERT(DATE, test_date, 101)) AS rn
    FROM person_tests
) t
GROUP BY person_id, person_name
ORDER BY person_id;

3. PostgreSQL #

推荐用条件聚合（简单通用），也可使用crosstab函数：

方式1：条件聚合 #

SELECT
    person_id,
    person_name,
    MAX(CASE WHEN rn = 1 THEN test_date END) AS test_date1,
    MAX(CASE WHEN rn = 2 THEN test_date END) AS test_date2,
    MAX(CASE WHEN rn = 3 THEN test_date END) AS test_date3,
    MAX(CASE WHEN rn = 4 THEN test_date END) AS test_date4
FROM (
    SELECT
        person_id,
        person_name,
        test_date,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY TO_DATE(test_date, 'MM/DD/YYYY')) AS rn
    FROM person_tests
) t
GROUP BY person_id, person_name
ORDER BY person_id;

方式2：crosstab函数（需启用扩展） #

-- 先启用tablefunc扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT
    person_id,
    person_name,
    test_date1,
    test_date2,
    test_date3,
    test_date4
FROM crosstab(
    'SELECT person_id, person_name, rn, test_date
     FROM (
         SELECT
             person_id,
             person_name,
             test_date,
             ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY TO_DATE(test_date, ''MM/DD/YYYY'')) AS rn
         FROM person_tests
     ) t
     ORDER BY 1, 3',
    'SELECT generate_series(1,4)'
) AS ct(
    person_id VARCHAR(6),
    person_name VARCHAR(20),
    test_date1 VARCHAR(10),
    test_date2 VARCHAR(10),
    test_date3 VARCHAR(10),
    test_date4 VARCHAR(10)
)
ORDER BY person_id;

注意事项 #

• 若test_date字段为DATE类型，可直接用字段排序，无需日期转换函数（如STR_TO_DATE）。
• 若用户的测试日期数量超过4个，需对应增加CASE语句或调整generate_series的范围。
• 可通过修改ORDER BY后的排序规则（如加DESC）调整日期的列对应顺序。

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