Combining multiple rows in postgreSQL into one row?

Question

I'm new to postgreSQL. My goal is to run a query that returns all the item_id's listed for each proposal_id, where each unique proposal_id is a separate row entry, and each item_id is a column in that row entry.

Table 1 data looks like this:

proposal_id    item_id  

1              83054
1              81048
2              71384
2              24282
2              19847
2              18482
3              84720
4              18081
4              73018

Here's what I want it to look like:

proposal_id     item_id_1     item_id_2    item_id_3    item_id_4    item_id_n
1                  83054      81048
2                  71384      24282        19847        18482
3                  84720
4                  18081      73018

The challenge is that I can have the same proposal_id for anywhere between 1 and up to 11 or more entries.

This is a simplified version for the purposes of asking this question. Once I nail this, my plan is to then join additional data for each item_id and proposal_id from other tables in here.

Any help greatly appreciated.

Thanks!

EDITED: Fixed table aesthetics

I think joining additional data would be easier with your existing table design. Can you show us what you want to join with? — cha
– cha, Commented Oct 30, 2014 at 22:31
Is the order of item_id important, within the same proposal_id? If yes, you should probably add an additional field with the order within the same proposal_id — Alexandros
– Alexandros, Commented Oct 30, 2014 at 22:47

klin · Accepted Answer · 2014-10-30 23:17:55Z

14

First, determine how many columns you need:

select proposal_id, array_length(array_agg(item_id), 1)
    from the_table
    group by 1
    order by 2 desc
    limit 1

Next, execute the following query with sufficient number of a's.

select proposal_id, a[1] id1, a[2] id2, a[3] id3, a[4] id4
from (
    select proposal_id, array_agg(item_id) a
    from the_table
    group by 1
    order by 1) z

SQLFiddle

edited Oct 30, 2014 at 23:17

answered Oct 30, 2014 at 23:05

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Nick Lashinsky Over a year ago

Thanks - this was really helpful. Curious, why is there a "z" at then end?

klin Over a year ago

A subquery in from clause must have an alias. I do not know why z:)

Collectives™ on Stack Overflow

Combining multiple rows in postgreSQL into one row?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related