Re: How to get array of unique array values across rows?

That worked perfectly. Thanks a lot!

On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park <luispark(at)gmail(dot)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(dot)tanzer(at)gmail(dot)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(dot)tanzer(at)agency-software(dot)org
>> (253) 245-3801
>>
>> Subscribe to the mailing list<agency-general-request(at)lists(dot)sourceforge(dot)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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.