Postgres Pro
Downloads
Home   >   mailing lists

Re: Need sql to pull data from terribly architected table - Mailing list pgsql-general

From David Johnston
Subject Re: Need sql to pull data from terribly architected table
Date
Msg-id 016901cdb154$ee641cc0$cb2c5640$@yahoo.com
Whole thread Raw
In response to Re: Need sql to pull data from terribly architected table  (Richard Broersma <richard.broersma@gmail.com>)
List pgsql-general
Tree view

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Broersma
Sent: Tuesday, October 23, 2012 3:24 PM
To: chris@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.broersma@gmail.com> wrote:

On Tue, Oct 23, 2012 at 12:06 PM, Chris Curvey <chris@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.

 

pgsql-general by date:

Previous
From: "Gauthier, Dave"
Date:
Subject: Re: Need sql to pull data from terribly architected table
Next
From: "David Johnston"
Date:
Subject: Re: Need sql to pull data from terribly architected table