Concatenate Child Rows While Respecting Where Clause With Or

Question

I have an issue where I need to concatenate the values of a child table into one field, while also respecting an OR condition in the where clause. Let's say I'm working in the Northwind database, and I have a query such as:

SELECT c.CategoryName, p.ProductName FROM Products p join Categories c on p.CategoryID = c.CategoryID
where c.CategoryName like '%on%' or p.ProductName = 'Vegie-spread'
order by c.CategoryName, p.ProductName

I want all the product names to be concatenated into one field for each category name, so that the Products field would look like this:

Aniseed Syrup-Chef Anton's Cajun Seasoning-Chef Anton's Gumbo Mix-etc.

My first attempt looks like this:

select c.CategoryName, ISNULL(products.line, '') AS ProductNames
    from Categories c
    cross apply (
        select CAST((select p.ProductName + '-'
        from products p
        where c.CategoryID = p.CategoryID
        and (c.CategoryName like '%on%' or p.ProductName = 'Vegie-spread')
        order by p.ProductName
        for xml path('')) as nvarchar(max)) line
    ) products
order by c.CategoryName

But that returns some categories that don't match the where conditions. I want the results to be just as if I typed this query:

SELECT c.CategoryName, p.ProductName FROM Products p join Categories c on p.CategoryID = c.CategoryID
where c.CategoryName like '%on%' or p.ProductName = 'Vegie-spread'
order by c.CategoryName, p.ProductName

except that I want one line per category with all the products concatenated that match the query.

Can someone show me how to do this?

Should the WHERE in your outer apply subquery match the WHERE at the end? Also, your first code snippet is looking at category and product name, but your larger code set is looking at product line. Is all of that correct and as it should be? — dfundako, May 10 '18 at 13:43
I have edit it to do that and I get categories I shouldn't get. — Karin, May 10 '18 at 14:49

Tab Alleman · Accepted Answer · 2018-05-10T17:18:26.480

0

"Brute Force" it. Start with your "as if" query:

SELECT c.CategoryName, p.ProductName FROM Products p join Categories c on p.CategoryID = c.CategoryID
where c.CategoryName like '%on%' or p.ProductName = 'Vegie-spread'
order by c.CategoryName, p.ProductName

And use that as a CTE or derived table, and then apply your FOR XML PATH logic to that CTE instead of the base table to "group concatenate" the product rows into one.

Here is a question that shows several ways of doing a "group concat" in SQL Server.

So in psuedocode, using a CTE would look like this:

WITH cte AS (
  {Your "as-if" query}
)
SELECT CategoryName, ({code that does a group concat}) AS ProductLine
FROM cte

And in the {code that does a group concat} part (however you decide to do it) you would similarly replace your actual table name with cte.

edited May 10 '18 at 17:18

answered May 10 '18 at 14:09

Tab Alleman

31,483
7
36
52

I don't know how to do that with CTE. I've read about it and used it in other situations, but I still don't know how to do it in this situation. If you have the Northwind database can you show me how you would do it? – Karin May 10 '18 at 16:22
What part of it don't you know how to do? You treat the CTE the same as any other table in your main query. I don't have the Northwind database, but I'll add some psuedocode to my answer to see if that helps. – Tab Alleman May 10 '18 at 17:16

Concatenate Child Rows While Respecting Where Clause With Or

1 Answers1