Concatenate Child Rows While Respecting Where Clause With Or

Question

question

Karin asked May 10, '18

Concatenate Child Rows While Respecting Where Clause With Or

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][1] 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?

[1]: https://msdn.microsoft.com/en-us/library/mt710790.aspx

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-05-10T19:58:37Z

Karin answered May 10, '18

I think this is the best I could do:

    WITH cte AS (
        SELECT c.CategoryID, c.CategoryName, p.ProductName FROM Products p right join Categories c on p.CategoryID = c.CategoryID
        where c.CategoryName like '%on%' or p.ProductName = 'Vegie-spread'  
    )
    SELECT CategoryName, ProductNames
    FROM cte AS extern
    CROSS APPLY
    (
        SELECT ProductName + '-'
        FROM cte AS intern
        WHERE extern.CategoryID = intern.CategoryID
        order by ProductName
        FOR XML PATH('')
    ) pre_trimmed (ProductNames)
    GROUP BY CategoryName, ProductNames;

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.

erlokeshsharma08 commented · May 11, 2018 at 07:43 AM

I don't have the query editor with me now. But try one more solution:-

SELECT productid, NAME = (SELECT STUFF((select ',' + ProductName + '-' + CategoryName from product 
 join Categories  on product.CategoryID = Categories.CategoryID where productid = p.Productid
 and
 (CategoryName like '%on%' or ProductName = 'Vegie-spread')
 FOR XML PATH('')),1,1,''))
FROM Product p GROUP BY ID

0 ·

question