PostgreSQL：如何将关联查询的行转换为列？

针对你要将demo_contact_custom_field中的自定义字段行转为单独列的需求，这里提供几种精准匹配你需求的方案：

静态字段方案（已知所有自定义字段ID） #

如果你的自定义字段ID是固定的，直接用CASE配合聚合函数就能实现，这是最直接的方式：

SELECT
    dc.id AS contact_id,
    -- 匹配第一个自定义字段，无值则返回NULL（显示为空）
    MAX(CASE WHEN dccf.custom_field_id = '7759512f-662f-4139-94fb-8b708c5d11eb' THEN dccf.value END) AS "7759512f-662f-4139-94fb-8b708c5d11eb",
    -- 匹配第二个自定义字段
    MAX(CASE WHEN dccf.custom_field_id = 'a96993bf-eb38-446c-a5a7-416485e8b933' THEN dccf.value END) AS "a96993bf-eb38-446c-a5a7-416485e8b933"
FROM demo_contact dc
-- LEFT JOIN保证所有contact都被包含，即使没有自定义字段值
LEFT JOIN demo_contact_custom_field dccf ON dc.id = dccf.contact_id
GROUP BY dc.id
ORDER BY dc.id;

这个查询会输出你想要的格式：每个自定义字段作为单独列，contact_id对应行，无值的单元格为空。用MAX聚合是因为每个contact_id + custom_field_id组合最多一条记录（如果有重复值，MAX会保留最后一条，你也可以根据需求换成MIN或STRING_AGG，但这里用MAX足够）。

动态字段方案（自定义字段ID不固定） #

如果你的自定义字段会动态新增，静态SQL就不够灵活了，这时候可以用PL/pgSQL生成动态SQL：

CREATE OR REPLACE FUNCTION pivot_contact_custom_fields()
RETURNS SETOF record AS $$
DECLARE
    field_ids text[];
    select_clause text;
    result_columns text;
BEGIN
    -- 获取所有唯一的自定义字段ID
    SELECT array_agg(DISTINCT custom_field_id ORDER BY custom_field_id) INTO field_ids 
    FROM demo_contact_custom_field;

    -- 构建SELECT子句中的字段映射
    select_clause := string_agg(
        'MAX(CASE WHEN dccf.custom_field_id = ''' || field_id || ''' THEN dccf.value END) AS "' || field_id || '"',
        ', '
    ) FROM unnest(field_ids) AS field_id;

    -- 构建结果列的定义字符串（用于RETURNS SETOF record的调用）
    result_columns := 'contact_id numeric, ' || string_agg('"' || field_id || '" text', ', ') FROM unnest(field_ids) AS field_id;

    -- 执行动态生成的SQL
    RETURN QUERY EXECUTE format(
        'SELECT dc.id AS contact_id, %s 
         FROM demo_contact dc 
         LEFT JOIN demo_contact_custom_field dccf ON dc.id = dccf.contact_id 
         GROUP BY dc.id 
         ORDER BY dc.id',
        select_clause
    );

    -- 返回结果列定义（调用时需要指定）
    RAISE NOTICE 'Result columns: %', result_columns;
END;
$$ LANGUAGE plpgsql;

调用这个函数时，需要指定结果列的结构（或者用hstore/JSON格式返回，更灵活）：

-- 调用方式：根据NOTICE输出的列定义替换这里的结构
SELECT * FROM pivot_contact_custom_fields() 
AS (contact_id numeric, "7759512f-662f-4139-94fb-8b708c5d11eb" text, "a96993bf-eb38-446c-a5a7-416485e8b933" text);

用tablefunc扩展的crosstab函数（官方透视表工具） #

你之前看到的crosstab其实正是PostgreSQL官方推荐的透视表解决方案，可能你之前误解了它的用法。首先需要启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后用crosstab实现你的需求：

SELECT * FROM crosstab(
    -- 第一个参数：源数据，按contact_id和custom_field_id排序
    'SELECT contact_id, custom_field_id, value 
     FROM demo_contact_custom_field 
     ORDER BY 1, 2',
    -- 第二个参数：所有要转为列的自定义字段ID
    'SELECT DISTINCT custom_field_id 
     FROM demo_contact_custom_field 
     ORDER BY 1'
) AS ct(
    contact_id numeric,
    "7759512f-662f-4139-94fb-8b708c5d11eb" text,
    "a96993bf-eb38-446c-a5a7-416485e8b933" text
);

这个方法和静态CASE方案效果一致，是PostgreSQL中专门处理行转列的工具，适合固定字段的场景；如果是动态字段，同样需要结合PL/pgSQL生成动态的crosstab查询。

关于你之前搜索的方案说明 #

你提到的那些链接要么是合并多行值到单个单元格（比如数组、字符串拼接），要么是错误的关联方式，所以不符合你的需求。而上面的CASE聚合、动态SQL生成、crosstab才是真正实现"将关联行转为单独列"的正确方向。

内容的提问来源于stack exchange，提问作者Tomáš Hübelbauer