From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Richard Broersma'" <richard(dot)broersma(at)gmail(dot)com>, <chris(at)chriscurvey(dot)com> |
Cc: | "'Gauthier, Dave'" <dave(dot)gauthier(at)intel(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need sql to pull data from terribly architected table |
Date: | 2012-10-23 19:30:59 |
Message-ID: | 016901cdb154$ee641cc0$cb2c5640$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Richard Broersma
Sent: Tuesday, October 23, 2012 3:24 PM
To: chris(at)chriscurvey(dot)com
Cc: Gauthier, Dave; pgsql-general(at)postgresql(dot)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(dot)broersma(at)gmail(dot)com> wrote:
On Tue, Oct 23, 2012 at 12:06 PM, Chris Curvey <chris(at)chriscurvey(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2012-10-23 19:31:14 | Re: Need sql to pull data from terribly architected table |
Previous Message | Richard Broersma | 2012-10-23 19:24:06 | Re: Need sql to pull data from terribly architected table |