PostgreSQL join 2 rows into 1 row

Question

If there is a duplicate please point me to it. I don't know what keywords to use to search for this situation.
I have this table:

name    | read  | sum1  | sum2 
--------+-------+-------+-----
A       | 1     | 2.0   | 1.3
A       | 2     | 0.4   | 4  
B       | 1     | 0.2   | 0.1
B       | 2     | 1.0   | 3.3

What I want is this:

name    | r1_sum1 | r1_sum2  | r2_sum1 | r2_sum2 
--------+---------+----------+---------+--------
A       | 2.0     | 1.3      | 0.4     | 4
B       | 0.2     | 0.1      | 1.0     | 3.3

So it's like the rows are grouped by the read, and name columns. How do I do this in psql?

Does read take a fixed number of values, or could the desired query potentially return arbitrarily many columns? — ruakh
– ruakh, Commented Jan 19, 2012 at 21:51
I'm not 100% sure what you mean by "in most cases" -- presumably you would like a query that covers all cases? -- but I've posted an answer based on my best guess of what you mean. — ruakh
– ruakh, Commented Jan 19, 2012 at 22:01

ruakh · Accepted Answer · 2012-01-19 21:59:16Z

1

You can join the table to itself:

SELECT t1.name AS name,
       t1.sum1 AS r1_sum1,
       t1.sum2 AS r1_sum2,
       t2.sum1 AS r2_sum1,
       t2.sum2 AS r2_sum2
  FROM insert_table_name_here AS t1
  FULL
 OUTER
  JOIN insert_table_name_here AS t2
    ON t1.name = t2.name
   AND t1.read = 1
   AND t2.read = 2
;

answered Jan 19, 2012 at 21:59

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL join 2 rows into 1 row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related