Re: Need sql to pull data from terribly architected table

["David Johnston" <polobo@xxxxxxxxx>]

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Richard Broersma
Sent: Tuesday, October 23, 2012 3:24 PM
To: chris@xxxxxxxxxxxxxxx
Cc: Gauthier, Dave; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Need sql to pull data from terribly architected table

 

 

On Tue, Oct 23, 2012 at 12:21 PM, Richard Broersma <richard.broersma@xxxxxxxxx> wrote:

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