From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Broersma Sent: Tuesday, October 23, 2012 3:24 PM To: ch...@chriscurvey.com Cc: Gauthier, Dave; pgsql-general@postgresql.org Subject: Re: [GENERAL] Need sql to pull data from terribly architected table
On Tue, Oct 23, 2012 at 12:21 PM, Richard Broersma <richard.broer...@gmail.com> wrote: On Tue, Oct 23, 2012 at 12:06 PM, Chris Curvey <ch...@chriscurvey.com> wrote: select my_ids.id , c1.value as col1 , c2.value as col2 , c3.value as col3 , c4.value as col4 , c5.value as col5 from my_ids left join foo c1 on my_ids.id = c1.id left join foo c2 on my_ids.id = c2.id left join foo c3 on my_ids.id = c3.id left join foo c4 on my_ids.id = c4.id left join foo c5 on my_ids.id = c5.id How about: oops - I had some malformed air code SELECT my_ids.id, ARRAY_AGG( ( property, value ) order by (property, value) ) FROM my_ids GROUP BY id ORDER BY id; -- Regards, Richard Broersma Jr. -- Regards, Richard Broersma Jr. The main problem with this code is that the length of the array varies between rows depending on which attributes are missing entries. You would want to generate dummy records for any missing attributes and then apply the ARRAY_AGG. David J.
