PostgreSQL：如何合并同表的多个SELECT查询结果为单行

Hey there! Let's work through how to get that single-row output you need. Here are two straightforward, standard SQL approaches that will do the trick:

Approach 1: Self-Join the Table #

Since each value in C1 (A, B, C) is unique, you can join the table to itself three times—each time filtering for a specific C1 value—to pull all required columns into one row directly.

SELECT
    t1.C2 AS 1_C2,
    t2.C2 AS 2_C2,
    t3.C2 AS 3_C2,
    t1.C3 AS 1_C3,
    t2.C3 AS 2_C3,
    t3.C3 AS 3_C3
FROM table1 t1
JOIN table1 t2 ON t1.C4 = t2.C4 AND t2.C1 = 'B'
JOIN table1 t3 ON t1.C4 = t3.C4 AND t3.C1 = 'C'
WHERE t1.C1 = 'A' AND t1.C4 = 'X';

Quick breakdown: #

• We alias the table three times (t1, t2, t3) to treat them as separate "copies" of the original data
• t1 targets rows where C1 = 'A', t2 targets C1 = 'B', and t3 targets C1 = 'C'
• Joining on C4 = 'X' ensures we only work with rows matching your original query's C4 condition
• The result is a single row with all the renamed columns you requested

Approach 2: Conditional Aggregation (More Scalable) #

If you might add more values to C1 later, conditional aggregation is a better, more flexible choice. It uses CASE WHEN inside aggregate functions like MAX() to pivot rows into columns.

SELECT
    MAX(CASE WHEN C1 = 'A' THEN C2 END) AS 1_C2,
    MAX(CASE WHEN C1 = 'B' THEN C2 END) AS 2_C2,
    MAX(CASE WHEN C1 = 'C' THEN C2 END) AS 3_C2,
    MAX(CASE WHEN C1 = 'A' THEN C3 END) AS 1_C3,
    MAX(CASE WHEN C1 = 'B' THEN C3 END) AS 2_C3,
    MAX(CASE WHEN C1 = 'C' THEN C3 END) AS 3_C3
FROM table1
WHERE C4 = 'X';

Quick breakdown: #

• The CASE WHEN statements check for each C1 value and return the corresponding C2 or C3 value (returning NULL for non-matching rows)
• MAX() (or MIN() works too, since each C1 has exactly one matching row) picks out the non-null value for each column
• We filter the entire table to only include rows where C4 = 'X' upfront, simplifying the overall logic

Expected Output #

Both queries will produce the exact single-row result you're aiming for:

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