Converting join'ed rows into a comma or space delimited list

On Thu, Aug 12, 2010 at 1:12 AM, Mike Christensen <mike@kitchenpc.com> wrote:

I have the table recipes (ID/Name):

1 - Pancakes
2 - Chicken dish
3 - Tacos

Then I have the table RecipeTags (RecipeID/Tag)

1 - A
1 - C
2 - A
2 - D
3 - E
3 - F
3 - G

I want to query for all recipes, but join in the recipe tags.
However, rather than having a row for each recipe tag, I want to
convert the list of tags into a varchar string with a delimiter
character.  Something like this:

1 - Pancakes - A C
2 - Chicken dish - A D
3 - Tacos - E F G

Is this possible?  The query doesn't have to be fast, since it will
only be run when my website loads for the first time, but I'm guessing
SQL will do it at least as fast as I could do it on the middle tier.
Thanks!

Mike

On Thu, Aug 12, 2010 at 1:37 AM, Thom Brown <thom@linux.com> wrote:

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