HANA BTP SQL实现DATE列转置为行的解决方案求助

针对你需要将DATE列转置为行，同时生成星期标识行与品牌数值行的需求，以下提供两种可行的SQL方案：

1. 静态列方案（适用于少量固定日期，如示例中的4天） #

如果只需处理固定的几个日期，直接使用CASE表达式配合UNION ALL即可实现：

-- 生成星期标识行
SELECT 
    'BRAND' AS DATE,
    MAX(CASE WHEN DATE = '2024-04-01' THEN WEEK END) AS "2024-04-01",
    MAX(CASE WHEN DATE = '2024-04-02' THEN WEEK END) AS "2024-04-02",
    MAX(CASE WHEN DATE = '2024-04-03' THEN WEEK END) AS "2024-04-03",
    MAX(CASE WHEN DATE = '2024-04-04' THEN WEEK END) AS "2024-04-04"
FROM SOURCE_TABLE
UNION ALL
-- 生成各品牌的数值行
SELECT 
    BRAND AS DATE,
    MAX(CASE WHEN DATE = '2024-04-01' THEN VALUE END) AS "2024-04-01",
    MAX(CASE WHEN DATE = '2024-04-02' THEN VALUE END) AS "2024-04-02",
    MAX(CASE WHEN DATE = '2024-04-03' THEN VALUE END) AS "2024-04-03",
    MAX(CASE WHEN DATE = '2024-04-04' THEN VALUE END) AS "2024-04-04"
FROM SOURCE_TABLE
GROUP BY BRAND

将代码中的SOURCE_TABLE替换为你的实际表名即可执行。

2. 动态列方案（适用于全年365天的动态日期） #

由于全年日期数量多，固定写法过于繁琐，可通过HANA动态SQL自动生成列：

DO BEGIN
    DECLARE v_date_week_cols NVARCHAR(10000);
    DECLARE v_date_value_cols NVARCHAR(10000);
    DECLARE v_full_sql NVARCHAR(10000);

    -- 生成星期行的列表达式
    SELECT STRING_AGG(
        'MAX(CASE WHEN DATE = ''' || DATE || ''' THEN WEEK END) AS "' || DATE || '"',
        ', '
    ) INTO v_date_week_cols
    FROM (SELECT DISTINCT DATE FROM SOURCE_TABLE ORDER BY DATE);

    -- 生成数值行的列表达式
    SELECT STRING_AGG(
        'MAX(CASE WHEN DATE = ''' || DATE || ''' THEN VALUE END) AS "' || DATE || '"',
        ', '
    ) INTO v_date_value_cols
    FROM (SELECT DISTINCT DATE FROM SOURCE_TABLE ORDER BY DATE);

    -- 拼接完整SQL语句
    v_full_sql := 'SELECT ''BRAND'' AS DATE, ' || v_date_week_cols || ' FROM SOURCE_TABLE '
        || 'UNION ALL SELECT BRAND AS DATE, ' || v_date_value_cols || ' FROM SOURCE_TABLE GROUP BY BRAND';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_full_sql;
END;

版本兼容说明： #

如果你的HANA版本不支持STRING_AGG（需HANA 2.0 SPS 05及以上），可替换为XMLAGG拼接字符串：

-- 替换星期行的列表达式生成逻辑
SELECT REPLACE(REPLACE(XMLAGG(XMLELEMENT(E, 'MAX(CASE WHEN DATE = ''' || DATE || ''' THEN WEEK END) AS "' || DATE || '", ')).GETSTRINGVAL(), '<E>', ''), '</E>', '') 
INTO v_date_week_cols
FROM (SELECT DISTINCT DATE FROM SOURCE_TABLE ORDER BY DATE);
-- 移除末尾多余的逗号
v_date_week_cols := SUBSTRING(v_date_week_cols, 1, LENGTH(v_date_week_cols) - 2);

-- 数值行的列表达式同理替换即可

记得将代码中的SOURCE_TABLE替换为你的实际表名。

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