Обсуждение: Re: Converting join'ed rows into a comma or space delimited list

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;

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 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

Thanks!  Yup the LEFT JOIN is probably a cleaner syntax over the nested query..

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
>