将同一Person_id的多行起止日期记录合并为单行多列的技术需求

嘿，这个需求我太熟悉了！本质上就是把同一Person_id+Store_id下的多行起止日期记录行转列（Pivot），不同数据库的实现思路大同小异，我给你几个主流数据库的解决方案，你按需取用：

核心思路 #

先给每组（Person_id+Store_id）的记录按Startdate排序编序号，再通过条件聚合（或数据库自带的Pivot函数）把每个序号对应的Startdate和enddate转成单独的列。

MySQL 解决方案 #

MySQL没有原生的Pivot函数，用条件聚合最直接：

SELECT 
    Person_id,
    Store_id,
    MAX(CASE WHEN seq = 1 THEN Startdate END) AS Startdate1,
    MAX(CASE WHEN seq = 1 THEN enddate END) AS enddate1,
    MAX(CASE WHEN seq = 2 THEN Startdate END) AS Startdate2,
    MAX(CASE WHEN seq = 2 THEN enddate END) AS enddate2,
    MAX(CASE WHEN seq = 3 THEN Startdate END) AS Startdate3,
    MAX(CASE WHEN seq = 3 THEN enddate END) AS enddate3,
    MAX(CASE WHEN seq = 4 THEN Startdate END) AS Startdate4,
    MAX(CASE WHEN seq = 4 THEN enddate END) AS enddate4
    -- 要是你的记录超过4条，继续加对应的CASE语句就行
FROM (
    SELECT 
        Person_id,
        Store_id,
        Startdate,
        enddate,
        -- 给每组记录按Startdate排序编序号
        ROW_NUMBER() OVER (PARTITION BY Person_id, Store_id ORDER BY Startdate) AS seq
    FROM your_table_name
    WHERE Person_id = '10000351067' -- 针对特定Person_id，去掉就处理所有用户
) t
GROUP BY Person_id, Store_id;

SQL Server 解决方案 #

SQL Server有两种常用方式，条件聚合最通用，也可以用原生的Pivot函数：

方式1：条件聚合（和MySQL逻辑一致） #

SELECT 
    Person_id,
    Store_id,
    MAX(CASE WHEN seq = 1 THEN Startdate END) AS Startdate1,
    MAX(CASE WHEN seq = 1 THEN enddate END) AS enddate1,
    MAX(CASE WHEN seq = 2 THEN Startdate END) AS Startdate2,
    MAX(CASE WHEN seq = 2 THEN enddate END) AS enddate2,
    MAX(CASE WHEN seq = 3 THEN Startdate END) AS Startdate3,
    MAX(CASE WHEN seq = 3 THEN enddate END) AS enddate3,
    MAX(CASE WHEN seq = 4 THEN Startdate END) AS Startdate4,
    MAX(CASE WHEN seq = 4 THEN enddate END) AS enddate4
FROM (
    SELECT 
        Person_id,
        Store_id,
        Startdate,
        enddate,
        ROW_NUMBER() OVER (PARTITION BY Person_id, Store_id ORDER BY Startdate) AS seq
    FROM your_table_name
    WHERE Person_id = '10000351067'
) t
GROUP BY Person_id, Store_id;

方式2：UNPIVOT + PIVOT 组合 #

适合需要转多个字段的场景：

WITH numbered AS (
    -- 先给每组记录编号
    SELECT 
        Person_id,
        Store_id,
        Startdate,
        enddate,
        ROW_NUMBER() OVER (PARTITION BY Person_id, Store_id ORDER BY Startdate) AS seq
    FROM your_table_name
    WHERE Person_id = '10000351067'
),
unpivoted AS (
    -- 把Startdate和enddate拆成键值对
    SELECT 
        Person_id,
        Store_id,
        CONCAT(col_name, seq) AS new_col,
        col_value
    FROM numbered
    UNPIVOT (
        col_value FOR col_name IN (Startdate, enddate)
    ) up
)
-- 把组合后的列名转成实际列
SELECT 
    Person_id,
    Store_id,
    Startdate1, enddate1, Startdate2, enddate2, Startdate3, enddate3, Startdate4, enddate4
FROM unpivoted
PIVOT (
    MAX(col_value) FOR new_col IN (Startdate1, enddate1, Startdate2, enddate2, Startdate3, enddate3, Startdate4, enddate4)
) p;

PostgreSQL 解决方案 #

PostgreSQL可以用条件聚合，也可以用专门的crosstab函数：

方式1：条件聚合（通用写法） #

SELECT 
    Person_id,
    Store_id,
    MAX(CASE WHEN seq = 1 THEN Startdate END) AS Startdate1,
    MAX(CASE WHEN seq = 1 THEN enddate END) AS enddate1,
    MAX(CASE WHEN seq = 2 THEN Startdate END) AS Startdate2,
    MAX(CASE WHEN seq = 2 THEN enddate END) AS enddate2,
    MAX(CASE WHEN seq = 3 THEN Startdate END) AS Startdate3,
    MAX(CASE WHEN seq = 3 THEN enddate END) AS enddate3,
    MAX(CASE WHEN seq = 4 THEN Startdate END) AS Startdate4,
    MAX(CASE WHEN seq = 4 THEN enddate END) AS enddate4
FROM (
    SELECT 
        Person_id,
        Store_id,
        Startdate,
        enddate,
        ROW_NUMBER() OVER (PARTITION BY Person_id, Store_id ORDER BY Startdate) AS seq
    FROM your_table_name
    WHERE Person_id = '10000351067'
) t
GROUP BY Person_id, Store_id;

方式2：使用crosstab函数 #

需要先安装tablefunc扩展：

-- 先执行安装扩展（只需一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    -- 拼接需要转列的数据源
    'SELECT Person_id, Store_id, CONCAT(''Startdate'', seq), Startdate
     FROM (
         SELECT Person_id, Store_id, Startdate,
                ROW_NUMBER() OVER (PARTITION BY Person_id, Store_id ORDER BY Startdate) AS seq
         FROM your_table_name
         WHERE Person_id = ''10000351067''
     ) t
     UNION ALL
     SELECT Person_id, Store_id, CONCAT(''enddate'', seq), enddate
     FROM (
         SELECT Person_id, Store_id, enddate,
                ROW_NUMBER() OVER (PARTITION BY Person_id, Store_id ORDER BY Startdate) AS seq
         FROM your_table_name
         WHERE Person_id = ''10000351067''
     ) t
     ORDER BY 1,2,3',
    -- 指定要生成的列名
    'VALUES (''Startdate1''), (''enddate1''), (''Startdate2''), (''enddate2''), (''Startdate3''), (''enddate3''), (''Startdate4''), (''enddate4'')'
) AS ct(Person_id text, Store_id text, Startdate1 date, enddate1 date, Startdate2 date, enddate2 date, Startdate3 date, enddate3 date, Startdate4 date, enddate4 date);

补充说明 #

如果你的记录数量不固定（比如有的用户有5条，有的只有2条），想要动态生成列的话，需要用动态SQL（比如MySQL的预处理语句、SQL Server的动态字符串拼接、PostgreSQL的PL/pgSQL）。不过如果是已知最多N条记录，静态的条件聚合写法足够简单好用。

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