PostgreSQL如何将翻译查询的多行结果按language_id转为多列

推荐使用条件聚合方案，无需使用crosstab，逻辑直观易排查：

方案1：手动指定语言列（最易调试） #

直接通过CASE WHEN+聚合函数实现，你可以根据实际需要的语言列增减对应逻辑，报错可直接定位到对应语言的判断逻辑：

WITH modules AS (
    SELECT *
    FROM translate_dev.tab_module AS m
             JOIN translate_dev.tab_translation_module AS tm ON m.id = tm.module_id
),
base_data AS (
    SELECT tm.name,
           t.id,
           t.language_id,
           t.text
    FROM translate_dev.tab_translation AS t
             JOIN modules AS tm ON tm.translation_id = t.id
    WHERE t.id IN (
        1166615, 1166401, 1166578, 1166579, 1166580, 1166581, 1166582, 1166583, 1166584, 1166586, 1166587,
        1166588, 1166589, 1166591, 1166595, 1166597, 1166598, 1166599, 1166600, 1166601, 1166602, 1166603,
        1166604, 1166605, 1166606, 1166607, 1166608, 1166610, 1166612, 1166614, 1166616, 1166617, 1166618,
        1166619, 1166621, 1166623, 1166624, 1166626, 1166627, 1166628, 1166629, 1166631, 1166632, 1166633,
        1166634, 1166635, 1166636, 1166637, 1166638, 1166640, 1166641, 1166642, 1166643, 1166644, 1166645,
        1166646, 1166650, 1166651, 1166652, 1166653, 1166654, 1166655, 1166656, 1166657, 1166658, 1166659,
        1166662, 1166664, 1166665, 1166667, 1166668, 1166669, 1166671, 1166672, 1166673, 1166674, 1166675,
        1166676, 1166677, 1166678, 1166679, 1166680, 1166681, 1166682, 1166683, 1166685, 1166688, 1166689,
        1166693, 1166696, 1166697, 1166698, 1166699, 1166700, 1166701, 1166702, 1166704, 1166705, 1166706,
        1166707, 1166709, 1166710, 1166712, 1166713, 1166714, 1166716, 1166717, 1166718, 1166719, 1166721,
        1166722, 1166723, 1166725, 1166726, 1166727, 1166728, 1166730, 1166731, 1166733, 1166734, 1166735,
        1166736, 1166741, 1166742, 1166743, 1166744, 1166745, 1166747, 1166748, 1166749, 1166751, 1166752,
        1166753, 1166754, 1166755, 1166756, 1166757, 1166758, 1166759, 1166760, 1167155, 1167157, 1167158,
        1167539, 1167540, 1167546, 1167966, 1167967, 1168007, 1168010, 1168011, 1168012, 1168014, 1168015,
        1168016, 1168017, 1168018, 1168019, 1168020, 1168021, 1168022, 1168023, 1168024, 1168025, 1168026,
        1168027, 1168028, 1168029, 1168030, 1168031, 1168032, 1168033, 1168034, 1168035
    )
)
SELECT 
    name,
    id,
    MAX(CASE WHEN language_id = 'bg' THEN text END) AS bg,
    MAX(CASE WHEN language_id = 'cs' THEN text END) AS cs,
    MAX(CASE WHEN language_id = 'de' THEN text END) AS de,
    -- 其余语言按照上述格式补充即可
    MAX(CASE WHEN language_id = 'en' THEN text END) AS en
FROM base_data
GROUP BY name, id
ORDER BY id;

该方案特点：

• 无需依赖额外数据库扩展，标准SQL语法所有关系型数据库都支持
• 报错直接指向对应列的逻辑，无需排查拼接的字符串SQL
• 可灵活调整聚合逻辑，例如同一id同一语言存在多条翻译时，将MAX替换为STRING_AGG(text, ',')即可合并展示所有翻译内容

方案2：动态生成列（适合语言较多场景） #

如果语言种类多、不想手动逐个写CASE WHEN，可以用动态SQL生成查询语句，执行前可以先打印生成的SQL内容单独调试，比crosstab的隐式拼接更易排查：

DO $$
DECLARE
    -- 存储动态生成的列逻辑
    column_sql TEXT;
    -- 存储最终执行的完整SQL
    full_sql TEXT;
BEGIN
    -- 生成所有语言对应的CASE WHEN列逻辑
    SELECT string_agg(
        format('MAX(CASE WHEN language_id = %L THEN text END) AS %I', language_id, language_id),
        ', ' ORDER BY language_id
    ) INTO column_sql
    FROM (SELECT DISTINCT language_id FROM translate_dev.tab_translation) t;

    -- 拼接完整查询SQL
    full_sql := format('
        WITH modules AS (
            SELECT *
            FROM translate_dev.tab_module AS m
            JOIN translate_dev.tab_translation_module AS tm ON m.id = tm.module_id
        )
        SELECT 
            tm.name,
            t.id,
            %s
        FROM translate_dev.tab_translation AS t
        JOIN modules AS tm ON tm.translation_id = t.id
        WHERE t.id IN (
            1166615, 1166401, 1166578, 1166579, 1166580, 1166581, 1166582, 1166583, 1166584, 1166586, 1166587,
            1166588, 1166589, 1166591, 1166595, 1166597, 1166598, 1166599, 1166600, 1166601, 1166602, 1166603,
            1166604, 1166605, 1166606, 1166607, 1166608, 1166610, 1166612, 1166614, 1166616, 1166617, 1166618,
            1166619, 1166621, 1166623, 1166624, 1166626, 1166627, 1166628, 1166629, 1166631, 1166632, 1166633,
            1166634, 1166635, 1166636, 1166637, 1166638, 1166640, 1166641, 1166642, 1166643, 1166644, 1166645,
            1166646, 1166650, 1166651, 1166652, 1166653, 1166654, 1166655, 1166656, 1166657, 1166658, 1166659,
            1166662, 1166664, 1166665, 1166667, 1166668, 1166669, 1166671, 1166672, 1166673, 1166674, 1166675,
            1166676, 1166677, 1166678, 1166679, 1166680, 1166681, 1166682, 1166683, 1166685, 1166688, 1166689,
            1166693, 1166696, 1166697, 1166698, 1166699, 1166700, 1166701, 1166702, 1166704, 1166705, 1166706,
            1166707, 1166709, 1166710, 1166712, 1166713, 1166714, 1166716, 1166717, 1166718, 1166719, 1166721,
            1166722, 1166723, 1166725, 1166726, 1166727, 1166728, 1166730, 1166731, 1166733, 1166734, 1166735,
            1166736, 1166741, 1166742, 1166743, 1166744, 1166745, 1166747, 1166748, 1166749, 1166751, 1166752,
            1166753, 1166754, 1166755, 1166756, 1166757, 1166758, 1166759, 1166760, 1167155, 1167157, 1167158,
            1167539, 1167540, 1167546, 1167966, 1167967, 1168007, 1168010, 1168011, 1168012, 1168014, 1168015,
            1168016, 1168017, 1168018, 1168019, 1168020, 1168021, 1168022, 1168023, 1168024, 1168025, 1168026,
            1168027, 1168028, 1168029, 1168030, 1168031, 1168032, 1168033, 1168034, 1168035
        )
        GROUP BY tm.name, t.id
        ORDER BY t.id;
    ', column_sql);

    -- 调试时可以先放开下面注释，打印生成的SQL，复制出来单独执行排查问题
    -- RAISE NOTICE '%', full_sql;

    -- 执行SQL
    EXECUTE full_sql;
END $$;

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