How do I concatenate multiple rows into a single string to return only one row?

Question

question

bjc18722 asked Nov 30, '18

How do I concatenate multiple rows into a single string to return only one row?

I have an existing query that pulls recipe information using multiple joins, but need to add allergen information. I have a query that pulls the data I need, but it returns multiple rows, one row for each allergen. I need a single row for each recipe that lists the allergens as a single string separated by commas.

This is what I have so far:

SELECT        MenRecipeHeader.RecipeNumber, MenRecipeHeader.RecipeName, InvAllergies.Allergy
FROM            InvItemsAllergens INNER JOIN
                         InvAllergies ON InvItemsAllergens.InvAllergyID = InvAllergies.InvAllergyID INNER JOIN
                         InvItems ON InvItemsAllergens.InvItemsID = InvItems.InvItemID INNER JOIN
                         MenFoodItems INNER JOIN
                         MenRecipeHeader ON MenFoodItems.MenFoodItemID = MenRecipeHeader.MenFoodItemID ON InvItems.MenFoodItemID = MenFoodItems.MenFoodItemID
WHERE        (MenRecipeHeader.RecipeNumber = N'60218')

But, it returns one row per allergen....

RecipeNumber	RecipeName			Allergy
60218		YOGURT PARFAIT BERRY ALC	Dairy
60218		YOGURT PARFAIT BERRY ALC	Egg
60218		YOGURT PARFAIT BERRY ALC	Treenuts
60218		YOGURT PARFAIT BERRY ALC	Wheat
60218		YOGURT PARFAIT BERRY ALC	Soy

I need a single row for each recipe where the allergens are all in a single string separated by commas. Like this: Dairy, Egg, Treenuts, Wheat, Soy

How can I revise the existing query to return the allergens so each recipe has only one row?

sql query subquery concatenation

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-12-01T14:04:41Z

Kev Riley answered Dec 1, '18

Here's an example that shows 2 ways of doing this on top of your result set. You could rework the query down into the actual select, it's just I don't have all the table definitions or sample data to be able to show you that.

declare @YourTable table (RecipeNumber int, RecipeName varchar(100), Allergy varchar(10))
insert into @YourTable (RecipeNumber, RecipeName, Allergy) select 60218,'YOGURT PARFAIT BERRY ALC','Dairy'
insert into @YourTable (RecipeNumber, RecipeName, Allergy) select 60218,'YOGURT PARFAIT BERRY ALC','Egg'
insert into @YourTable (RecipeNumber, RecipeName, Allergy) select 60218,'YOGURT PARFAIT BERRY ALC','Treenuts'
insert into @YourTable (RecipeNumber, RecipeName, Allergy) select 60218,'YOGURT PARFAIT BERRY ALC','Wheat'
insert into @YourTable (RecipeNumber, RecipeName, Allergy) select 60218,'YOGURT PARFAIT BERRY ALC','Soy'

If you are using SQL Server 2017, there's the function STRING_AGG()

select RecipeNumber, RecipeName, string_agg(Allergy,',')
from @YourTable
group by RecipeNumber, RecipeName

RecipeNumber RecipeName                 Allergies
------------ -------------------------  --------------------------------
60218        YOGURT PARFAIT BERRY ALC   Dairy,Egg,Treenuts,Wheat,Soy

(1 row affected)

Otherwise you can use a well known XML/stuff 'trick'

select  distinct RecipeNumber, RecipeName,
    stuff ( ( SELECT
                  ',' + Allergy 
              FROM
                  @YourTable YT2
              WHERE YT1.RecipeNumber = YT2.RecipeNumber
    FOR XML PATH ( '' ) ) , 1 , 1 , '' ) as Allergies
from @YourTable YT1

RecipeNumber RecipeName                 Allergies
------------ -------------------------  --------------------------------
60218        YOGURT PARFAIT BERRY ALC   Dairy,Egg,Treenuts,Wheat,Soy

(1 row affected)

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bjc18722 commented · Dec 03, 2018 at 03:50 PM

Thank you Kev, I think we're getting close, but the recipe data is contained separate from the allergens, and links through a few other tables. Here's the definition of the original query.

Here's my attempt using your example, but it still returns 5 separate records - one for each allergen.

SELECT        MenRecipeHeader.RecipeNumber, MenRecipeHeader.RecipeName,
    stuff ( ( SELECT ',' + Allergy FROM InvAllergies A2 WHERE A1.InvAllergyID = A2.InvAllergyID
    FOR XML PATH ( '' ) ) , 1 , 1 , '' ) as Allergies
FROM            InvItemsAllergens A1 INNER JOIN
                         InvAllergies ON A1.InvAllergyID = InvAllergies.InvAllergyID INNER JOIN
                         InvItems ON A1.InvItemsID = InvItems.InvItemID INNER JOIN
                         MenFoodItems INNER JOIN
                         MenRecipeHeader ON MenFoodItems.MenFoodItemID = MenRecipeHeader.MenFoodItemID ON InvItems.MenFoodItemID = MenFoodItems.MenFoodItemID
WHERE        (MenRecipeHeader.RecipeNumber = N'60218')

Thank you for your help with this!

0 ·

annotation-2018-12-03-104705.jpg (70.8 KiB)

Kev Riley ♦♦ commented · Dec 03, 2018 at 03:56 PM

If you could share some DDL for the tables and some sample data in those, it will be easier for me to generate the query - alternatively you could just wrap my suggestion around your query as a subquery, or even as a CTE

0 ·

bjc18722 Kev Riley ♦♦ commented · Dec 04, 2018 at 05:31 PM

Hi Kev, here is the table definition with some sample data you can use (text file attached). The technique makes sense, but I can't seem to connect all the dots.allergensampledata.txt

0 ·

allergensampledata.txt (3.2 KiB)

Kev Riley ♦♦ bjc18722 commented · Dec 08, 2018 at 06:36 PM

Given the complexity of the original query (a 5 table join), I'd probably recommend simply wrapping that as a cte and then doin the concatenation on top of that, so for SQL 2017 that would be:

with originalquery_cte as (
select
	MenRecipeHeader.RecipeNumber, MenRecipeHeader.RecipeName, InvAllergies.Allergy
FROM            
	InvItemsAllergens 
	join InvAllergies ON InvItemsAllergens.InvAllergyID = InvAllergies.InvAllergyID 
	join InvItems ON InvItemsAllergens.InvItemsID = InvItems.InvItemID
	join MenFoodItems on InvItems.MenFoodItemID = MenFoodItems.MenFoodItemID
	join MenRecipeHeader ON MenFoodItems.MenFoodItemID = MenRecipeHeader.MenFoodItemID 


WHERE (MenRecipeHeader.RecipeNumber = N'60218')
)

select RecipeNumber, RecipeName, string_agg(Allergy,',')
from originalquery_cte
group by RecipeNumber, RecipeName

0 ·

Show more comments

Answer 2 · 2018-12-11T12:23:18Z

ThomasRushton answered Dec 11, '18

To make Kev's answer a bit safer, you might want to change the SELECT STUFF to the following form:

SELECT STUFF((SELECT … FOR XML PATH (''), type).value(‘.’,’varchar(max)’),1,1,,'')

(and you'll almost certainly need to retype that, as the apostrophes / inverted commas have been mangled by C&P)

That will protect you against xml translating `&`s into `&`s

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Dec 11, 2018 at 12:23 PM

that second & should be & amp;, but, yeah.

0 ·

question