如何在Snowflake中转置表格以按SOURCE列对比字段

原表（假设表名为your_table） #

目标表 #

解决方案 #

可以通过**先拆分行（Unpivot）再转换列（Pivot）**的方式实现，以下是不同数据库的具体写法：

1. SQL Server 写法

SELECT 
    FIELD,
    A,
    B
FROM (
    -- 将每列拆分为单独的行，标记字段名称
    SELECT 
        SOURCE,
        'ID' AS FIELD, CAST(ID AS VARCHAR(50)) AS VALUE
    FROM your_table
    UNION ALL
    SELECT 
        SOURCE,
        'NAME' AS FIELD, NAME AS VALUE
    FROM your_table
    UNION ALL
    SELECT 
        SOURCE,
        'DATE' AS FIELD, CAST(CURRENT_DATE AS VARCHAR(50)) AS VALUE
    FROM your_table
) AS unpivoted
-- 将SOURCE的A/B转换为列
PIVOT (
    MAX(VALUE)
    FOR SOURCE IN (A, B)
) AS pivoted;

2. MySQL 写法

MySQL无原生PIVOT语法，使用条件聚合实现：

SELECT 
    FIELD,
    MAX(CASE WHEN SOURCE = 'A' THEN VALUE END) AS A,
    MAX(CASE WHEN SOURCE = 'B' THEN VALUE END) AS B
FROM (
    SELECT 
        SOURCE,
        'ID' AS FIELD, CAST(ID AS CHAR) AS VALUE
    FROM your_table
    UNION ALL
    SELECT 
        SOURCE,
        'NAME' AS FIELD, NAME AS VALUE
    FROM your_table
    UNION ALL
    SELECT 
        SOURCE,
        'DATE' AS FIELD, DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d') AS VALUE
    FROM your_table
) AS unpivoted
GROUP BY FIELD;

3. PostgreSQL 写法

使用crosstab函数（需先启用tablefunc扩展）：

-- 启用扩展（仅首次执行）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT FIELD, SOURCE, VALUE
     FROM (
         SELECT 
             SOURCE,
             ''ID'' AS FIELD, CAST(ID AS TEXT) AS VALUE
         FROM your_table
         UNION ALL
         SELECT 
             SOURCE,
             ''NAME'' AS FIELD, NAME AS VALUE
         FROM your_table
         UNION ALL
         SELECT 
             SOURCE,
             ''DATE'' AS FIELD, CAST(CURRENT_DATE AS TEXT) AS VALUE
         FROM your_table
     ) AS unpivoted
     ORDER BY 1,2',
    'SELECT DISTINCT SOURCE FROM your_table ORDER BY 1'
) AS ct(FIELD TEXT, A TEXT, B TEXT);

说明 #

• 上述写法针对固定的SOURCE值（A、B），如果SOURCE值不固定，可通过动态SQL生成对应的列逻辑。
• 使用MAX()聚合是因为每个FIELD + SOURCE组合仅存在唯一值，聚合函数仅用于合并行。

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