RE: help with aggregation query across a second text array column

Thank you very much Rob, that concept worked out nicely. I would never have thought of unioning the table to itself with unnest.

Here's my final query:

select environment ||':' || string_agg(name, ',')
from (
select name,environment from servers union select name,unnest(auditenvironment) as environment from servers order by name) t
group by environment order by environment;

Cheers!

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot(dot)Kreienkamp(at)la-z-boy(dot)com
> -----Original Message-----
> From: Rob Nikander [mailto:rob(dot)nikander(at)gmail(dot)com]
> Sent: Monday, November 12, 2018 10:30 AM
> To: Postgres General <pgsql-general(at)postgresql(dot)org>
> Cc: Scot Kreienkamp <Scot(dot)Kreienkamp(at)la-z-boy(dot)com>
> Subject: Re: help with aggregation query across a second text array column
>
>
>
> > On Nov 12, 2018, at 9:40 AM, Scot Kreienkamp <Scot(dot)Kreienkamp(at)la-z-
> boy.com> wrote:
> > …
>
> I’m not too confident in my answer here (there could be a better way), but this
> might help. You could use the `unnest` function to transform the array into
> multiple rows. For example, given a table like
>
> create table t1 (env text, cls text, cls2 text[]);
>
> I can query it like:
>
> select env, string_agg(cls, ‘,’)
> from (select env, cls from t1
> union
> select env, unnest(cls2) from t1) t
> group by env;
>
> Rob

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.