That worked perfectly. Thanks a lot! On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park <luisp...@gmail.com> wrote:
> Try this one. > > select X.client_id, array_agg(X.color) > from (select distinct client_id, unnest(service_codes) as color > from foo) X > group by X.client_id; > > > On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer <ken.tan...@gmail.com> wrote: > >> I have a field containing a set of codes in a varchar array, each tied to >> a person. >> >> client_id | integer >> | >> service_codes | character varying(10)[] | >> >> I'm trying to query this info so that I can get the list (presumably in >> an array) of all the values in this array, across all the records for a >> client. So that if a person has two records, one with ORANGE and BLUE, and >> one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and >> GREEN. >> >> I had hopes for: >> >> SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; >> >> But was rebuffed with "ERROR: could not find array type for data type >> character varying[]" >> >> There's probably an easy answer for this, but it's completely escaping >> me. Any help appreciated. Thanks. >> >> Ken >> >> -- >> AGENCY Software >> A data system that puts you in control >> 100% Free Software >> *http://agency-software.org/* >> ken.tan...@agency-software.org >> (253) 245-3801 >> >> Subscribe to the mailing >> list<agency-general-requ...@lists.sourceforge.net?body=subscribe> >> to >> learn more about AGENCY or >> follow the discussion. >> > > -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list<agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
