PostgreSQL select query 'join multiple rows to one row'

Question

I am new to PostgreSQL. My case is that I have a table where I store my data. The data come from a file as one row and are getting saved in the database as 5 rows. What I want is to make a SELECT statement where it will combine the 5 rows again into one.

e.g.

id  id2  id3  year  code   value
4   1    1    1642  radio  30
4   1    1    1642  tv     56
4   1    1    1642  cable  67
4   1    1    1642  dine   70

I want to have a query where it will return the following:

id  id2  id3  year  radio  tv  cable dine
4   1    1    1642  30     56  67   70

The values of the code are becoming columns with values the actual values.

Is this possible?

Linger · Accepted Answer · 2014-09-11 13:11:42Z

5

You could use (SQL Fiddle):

SELECT m.id, m.id2, m.id3, m.year,
  SUM(CASE WHEN m.code = 'radio' THEN m.value END) as radio,
  SUM(CASE WHEN m.code = 'tv' THEN m.value END) as tv,
  SUM(CASE WHEN m.code = 'cable' THEN m.value END) as cable,
  SUM(CASE WHEN m.code = 'dine' THEN m.value END) as dine
FROM MyTable m
GROUP BY m.id, m.id2, m.id3, m.year

edited Sep 11, 2014 at 13:11

answered Sep 11, 2014 at 12:57

Linger

15.1k23 gold badges54 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL select query 'join multiple rows to one 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