Re: Converting join'ed rows into a comma or space delimited list - Mailing list pgsql-general

From Thom Brown
Subject Re: Converting join'ed rows into a comma or space delimited list
Date
Msg-id AANLkTi=Gdz=qASCf0VfxuwNL1fshD2R7nC3JgJ5th9C2@mail.gmail.com
Whole thread Raw
In response to Re: Converting join'ed rows into a comma or space delimited list  (Mike Christensen <mike@kitchenpc.com>)
Responses Re: Converting join'ed rows into a comma or space delimited list
List pgsql-general
On 12 August 2010 09:16, Mike Christensen <mike@kitchenpc.com> wrote:
> Damn I'm the master at posting stuff then figuring it out like 5
> seconds later..  Is there an approach better than this?
>
> select RecipeId, Rating, array_to_string(ARRAY(select Tag from
> RecipeTags where RecipeId = R.RecipeId), ' ') from Recipes R;
>
How about this:

SELECT recipes.recipeid, recipes.name,
array_to_string(array_agg(recipetags.tag), ' ')
FROM recipes
LEFT JOIN recipetags ON recipes.recipeid = recipetags.recipeid
GROUP BY recipes.recipeid, recipes.name;

Although in 9.0 you'll be able to do this:

SELECT recipes.recipeid, recipes.name, string_agg(recipetags.tag, ' '
ORDER BY recipetags.tag)
FROM recipes
LEFT JOIN recipetags ON recipes.recipeid = recipetags.recipeid
GROUP BY recipes.recipeid, recipes.name;

--
Thom Brown
Registered Linux user: #516935

pgsql-general by date:

Previous
From: Mike Christensen
Date:
Subject: Converting join'ed rows into a comma or space delimited list
Next
From: Torsten Zühlsdorff
Date:
Subject: Re: InitDB: Bad system call