求HANA BTP原生SQL实现日期列转置（月/年维度）的方案

需求说明 #

将如下结构的源表，把DATE列的日期值转置为表头列，生成指定格式的结果表。此前尝试PIVOT和日期序列函数未生效，需原生SQL实现。

源表

预期结果表

解决方案 #

1. 静态转置（固定日期范围）

如果日期是固定的，直接用CASE WHEN结合聚合函数实现，这是HANA完全支持的原生写法：

WITH week_row AS (
    SELECT
        'BRAND' AS DATE,
        MAX(CASE WHEN DATE = '2024-04-01' THEN 星期 END) AS "2024-04-01",
        MAX(CASE WHEN DATE = '2024-04-02' THEN 星期 END) AS "2024-04-02",
        MAX(CASE WHEN DATE = '2024-04-03' THEN 星期 END) AS "2024-04-03",
        MAX(CASE WHEN DATE = '2024-04-04' THEN 星期 END) AS "2024-04-04"
    FROM your_table_name
),
brand_rows AS (
    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 your_table_name
    GROUP BY BRAND
)
SELECT * FROM week_row
UNION ALL
SELECT * FROM brand_rows
ORDER BY DATE;

说明：

• week_row CTE生成首行的星期信息，通过MAX(CASE...)提取对应日期的星期值
• brand_rows CTE按品牌分组，聚合出每个品牌在对应日期的数值
• 最后用UNION ALL合并两部分结果，按DATE列排序得到目标格式

2. 动态转置（日期范围不固定）

如果日期是动态变化的，用HANA的动态SQL自动生成列：

DO BEGIN
    DECLARE date_columns NVARCHAR(2000);
    DECLARE dynamic_sql NVARCHAR(4000);

    -- 生成所有日期对应的CASE语句片段
    SELECT STRING_AGG(
        'MAX(CASE WHEN DATE = ''' || DATE || ''' THEN {COL} END) AS "' || DATE || '"',
        ', '
    ) INTO date_columns
    FROM (SELECT DISTINCT DATE FROM your_table_name) unique_dates;

    -- 拼接完整SQL
    dynamic_sql = 
        'WITH week_row AS (
            SELECT ''BRAND'' AS DATE, ' || REPLACE(date_columns, '{COL}', '星期') || ' 
            FROM your_table_name
        ),
        brand_rows AS (
            SELECT BRAND AS DATE, ' || REPLACE(date_columns, '{COL}', 'VALUE') || ' 
            FROM your_table_name
            GROUP BY BRAND
        )
        SELECT * FROM week_row UNION ALL SELECT * FROM brand_rows ORDER BY DATE';

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

说明：

• STRING_AGG自动聚合所有唯一日期，生成对应的CASE子句
• 通过REPLACE分别替换星期和数值的列名，复用代码片段
• 执行生成的动态SQL，自动适配任意日期范围

注意事项 #

• 将代码中的your_table_name替换为实际表名
• 动态SQL需要当前用户拥有EXECUTE IMMEDIATE权限

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