如何在ClickHouse中转置查询结果（行转列）

一、静态转置（已知列/行标识） #

适用于提前明确需要转置的列名和行分组标识的场景，全程不改变数据类型，可封装为视图供父查询重复调用。

1. 拆分行为键值对（Unpivot） #

用arrayJoin将每行的多列转换为多行的键值对结构：

SELECT
    id, -- 原数据中的行唯一标识，按需替换
    arrayJoin([
        ('col1', col1), -- 替换为你的实际列名
        ('col2', col2),
        ('col3', col3)
    ]) AS (metric, value)
FROM your_table -- 替换为你的原表或原查询语句

2. 聚合生成转置列（Pivot） #

用maxIf（或anyIf，因每个metric+id组合唯一）聚合每个指标对应的不同行标识值，生成转置后的列：

SELECT
    metric,
    maxIf(value, id = 1) AS id_1, -- 替换为你的实际行标识值
    maxIf(value, id = 2) AS id_2,
    maxIf(value, id = 3) AS id_3
FROM (
    -- 上述Unpivot子查询
    SELECT
        id,
        arrayJoin([('col1', col1), ('col2', col2), ('col3', col3)]) AS (metric, value)
    FROM your_table
)
GROUP BY metric

3. 封装为视图（重复使用） #

将转置逻辑创建为视图，后续父查询可直接调用：

CREATE VIEW transposed_view AS
SELECT
    metric,
    maxIf(value, id = 1) AS id_1,
    maxIf(value, id = 2) AS id_2,
    maxIf(value, id = 3) AS id_3
FROM (
    SELECT
        id,
        arrayJoin([('col1', col1), ('col2', col2), ('col3', col3)]) AS (metric, value)
    FROM your_table
)
GROUP BY metric

父查询使用示例：

-- 直接查询转置结果
SELECT * FROM transposed_view;

-- 关联其他表
SELECT tv.*, ot.other_column
FROM transposed_view tv
JOIN other_table ot ON tv.metric = ot.metric;

二、动态转置（列/行标识不固定） #

若行标识（如id）或列名动态变化，可通过动态SQL自动生成转置逻辑，在DBeaver中可保存为SQL片段重复执行：

EXECUTE IMMEDIATE (
    SELECT
        concat(
            'SELECT metric, ',
            -- 生成每个行标识对应的聚合列
            stringAgg(concat('maxIf(value, id = ', toString(id), ') AS id_', toString(id)), ', '),
            ' FROM (SELECT id, arrayJoin([',
            -- 生成所有需转置列的键值对
            stringAgg(concat('(\'', name, '\', ', name, ')'), ', '),
            ']) AS (metric, value) FROM your_table) GROUP BY metric'
        )
    FROM (
        -- 获取所有唯一行标识
        SELECT DISTINCT id FROM your_table
    ) AS ids,
    (
        -- 获取所有需转置的列（排除行标识列）
        SELECT name FROM system.columns 
        WHERE table = 'your_table' AND database = 'your_database' AND name != 'id'
    ) AS cols
)

替换your_table和your_database为实际名称即可自动生成并执行转置查询，且保留原数据类型。

三、单行转多行的简单转置 #

若仅需将单行多列转为多行键值对（无列聚合需求），直接用arrayJoin实现：

SELECT
    metric,
    value
FROM (
    SELECT
        arrayJoin([
            ('col1', col1),
            ('col2', col2),
            ('col3', col3)
        ]) AS (metric, value)
    FROM your_single_row_query -- 替换为你的单行输出查询
)

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