Oracle SQL如何将单列数据拆分扩展为多列？

原始数据表 #

目标输出表 #

需要将每个用户的多条记录转成一行多列的形式，示例如下：

现有代码问题 #

我尝试用以下SQL实现，但仅能拆分出第一组字段，无法生成多组ACT_DAY和OFFER_NAME列：

select user_id,
      Substr (offer_names, 1 , instr (offer_names, ',') - 1) as offer_name,
      Substr (act_dates, 1 , instr (act_dates, ',') - 1) as act_date,
      substr (offer_names, instr ( offer_names, ',') + 1) as next_offer_name,
      substr (act_dates, instr ( act_dates, ',') + 1) as next_act_date
from
(select u.user_id,,
       regexp_replace(listagg(o.offer_name, ',') within GROUP(ORDER BY o.day desc), '([^,]*)(,\1)+($|,)', '\1\3') as offer_names,
       regexp_replace(listagg(o.day, ',') within GROUP(ORDER BY o.day desc), '([^,]*)(,\1)+($|,)', '\1\3') as act_dates
   from user u
left join offer o on u.offer_id=o.id
    where user = '111'
group by user)

可行解决方案 #

方案一：静态PIVOT（适用于记录数固定的场景） #

如果每个用户的最大记录数已知（比如最多4条），可以先给每条记录按日期排序加序号，再用条件聚合实现转列：

WITH ranked_offers AS (
    SELECT 
        user,
        ACT_DAY,
        OFFER_NAME,
        -- 按ACT_DAY升序给每条记录编号
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY ACT_DAY) AS rn
    FROM your_table
    WHERE user = '111'
)
SELECT 
    user,
    MAX(CASE WHEN rn = 1 THEN ACT_DAY END) AS ACT_DAY_1,
    MAX(CASE WHEN rn = 1 THEN OFFER_NAME END) AS OFFER_NAME_1,
    MAX(CASE WHEN rn = 2 THEN ACT_DAY END) AS ACT_DAY_2,
    MAX(CASE WHEN rn = 2 THEN OFFER_NAME END) AS OFFER_NAME_2,
    MAX(CASE WHEN rn = 3 THEN ACT_DAY END) AS ACT_DAY_3,
    MAX(CASE WHEN rn = 3 THEN OFFER_NAME END) AS OFFER_NAME_3,
    MAX(CASE WHEN rn = 4 THEN ACT_DAY END) AS ACT_DAY_4,
    MAX(CASE WHEN rn = 4 THEN OFFER_NAME END) AS OFFER_NAME_4
FROM ranked_offers
GROUP BY user;

方案二：动态SQL（适用于记录数不固定的场景） #

如果用户的记录数不固定，需要用动态SQL自动生成对应数量的列：

DECLARE
    v_sql VARCHAR2(4000);
    v_cols VARCHAR2(2000);
BEGIN
    -- 动态生成需要的列表达式
    SELECT LISTAGG(
        'MAX(CASE WHEN rn = ' || rn || ' THEN ACT_DAY END) AS ACT_DAY_' || rn || ', ' ||
        'MAX(CASE WHEN rn = ' || rn || ' THEN OFFER_NAME END) AS OFFER_NAME_' || rn
    , ', ') WITHIN GROUP (ORDER BY rn)
    INTO v_cols
    FROM (
        SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY user ORDER BY ACT_DAY) AS rn
        FROM your_table
        WHERE user = '111'
    );

    -- 拼接完整SQL语句
    v_sql := '
        WITH ranked_offers AS (
            SELECT 
                user,
                ACT_DAY,
                OFFER_NAME,
                ROW_NUMBER() OVER (PARTITION BY user ORDER BY ACT_DAY) AS rn
            FROM your_table
            WHERE user = ''111''
        )
        SELECT user, ' || v_cols || '
        FROM ranked_offers
        GROUP BY user';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
END;
/

额外说明 #

1. 原代码中regexp_replace的去重逻辑会丢失重复的offer记录（比如示例中的两次offer_1），如果不需要去重，直接去掉该函数，保留listagg(o.offer_name, ',') within GROUP(ORDER BY o.day desc)即可。
2. 原代码子查询里的u.user_id,,多了一个逗号，属于语法错误，需要修正。

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