从连接表中以行的形式获取列，而不是以相邻列的形式获取列

DO $$
DECLARE
    column_names TEXT := '';
    query TEXT := 'SELECT ';
BEGIN
    FOR column_name IN SELECT DISTINCT column_name FROM your_table LOOP
        column_names := column_names || column_name || ', ';
    END LOOP;
    column_names := SUBSTRING(column_names FROM 1 FOR LENGTH(column_names) - 2);
    query := query || column_names || ' FROM your_table GROUP BY id';
    EXECUTE query;
END $$;

SELECT
    COALESCE(column1, 'N/A') AS column1,
    COALESCE(column2, 'N/A') AS column2
FROM (
    SELECT
        column1,
        column2,
        COUNT(*) AS count
    FROM your_table
    GROUP BY column1, column2
) AS subquery;

SELECT
    id,
    MAX(CASE WHEN category = 'A' THEN value ELSE NULL END) AS category_A,
    MAX(CASE WHEN category = 'B' THEN value ELSE NULL END) AS category_B
FROM your_table
GROUP BY id;