如何在Salesforce Analytics Studio中实现SQL表的行列转置？

首先修正你原查询中的笔误：表结构里列名为COST而非COSTS，计算PROFIT时的_COSTS是多余的下划线，正确的聚合查询应为：

SELECT
    REGION_NAME
    ,sum(CA) as CA
    ,sum(COST) as COST
    ,(sum(CA) - sum(COST)) as PROFIT
FROM "DATASET_NAME" 
GROUP BY REGION_NAME

要将结果转换为你想要的行列互换格式，确实需要结合UNPIVOT（先将指标列转为行）和PIVOT（再将区域行转为列）操作，完整查询如下：

WITH aggregated_data AS (
    SELECT
        REGION_NAME
        ,sum(CA) as CA
        ,sum(COST) as COST
        ,(sum(CA) - sum(COST)) as PROFIT
    FROM "DATASET_NAME" 
    GROUP BY REGION_NAME
)
SELECT
    Type
    ,FR
    ,UK
FROM aggregated_data
UNPIVOT (
    Value FOR Type IN (CA, COST, PROFIT)
) AS unpivoted
PIVOT (
    MAX(Value) FOR REGION_NAME IN ('FR' AS FR, 'UK' AS UK)
) AS pivoted
ORDER BY 
    CASE Type 
        WHEN 'PROFIT' THEN 1
        WHEN 'CA' THEN 2
        WHEN 'COST' THEN 3
    END;

逻辑说明： #

1. CTE aggregated_data：先按区域计算出每个指标的聚合值，得到你最初的分组结果。
2. UNPIVOT：把CA、COST、PROFIT这三列拆分为Type（指标名称）和Value（指标值）两行，让每个区域的三个指标变为三条独立记录。
3. PIVOT：把REGION_NAME的取值（FR、UK）转为列，通过MAX(Value)提取每个指标对应区域的数值（因为每个指标+区域只有唯一值，用MAX/SUM都可以）。
4. ORDER BY：控制Type的显示顺序，让结果和你期望的格式完全匹配。

如果你的环境对CTE支持有限，可以改用子查询形式：

SELECT
    Type
    ,FR
    ,UK
FROM (
    SELECT
        REGION_NAME
        ,sum(CA) as CA
        ,sum(COST) as COST
        ,(sum(CA) - sum(COST)) as PROFIT
    FROM "DATASET_NAME" 
    GROUP BY REGION_NAME
) AS aggregated_data
UNPIVOT (
    Value FOR Type IN (CA, COST, PROFIT)
) AS unpivoted
PIVOT (
    MAX(Value) FOR REGION_NAME IN ('FR' AS FR, 'UK' AS UK)
) AS pivoted
ORDER BY 
    CASE Type 
        WHEN 'PROFIT' THEN 1
        WHEN 'CA' THEN 2
        WHEN 'COST' THEN 3
    END;

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