👀 Views: 3 💬 Answers: 1 📅 Created: 2025-06-05

sql-server string-agg subquery sql

I'm wondering if anyone has experience with I've been working on this all day and I'm stuck on something that should probably be simple. I'm relatively new to this, so bear with me. I'm working with an scenario while trying to aggregate strings using `STRING_AGG` with an `ORDER BY` clause inside a subquery. The goal is to get a concatenated list of product names for each category, ordered by their `ProductID`. However, I'm working with an behavior that states: "Invalid column name 'ProductID'." My current query looks like this: ```sql SELECT c.CategoryID, (SELECT STRING_AGG(p.ProductName, ', ') WITHIN GROUP (ORDER BY p.ProductID) FROM Products p WHERE p.CategoryID = c.CategoryID) AS ProductNames FROM Categories c; ``` I expected to retrieve a list of product names ordered by their ID for each category, but instead, I'm getting the mentioned behavior. I've verified that `ProductID` exists in the `Products` table. When I try to run the subquery independently, it works fine, but within the main query, it raises this behavior. I have also tried adding the `ORDER BY` clause in the outer query, but it doesn't yield the desired output. Is there a restriction on using `ORDER BY` within `STRING_AGG` in a subquery like this? How can I resolve this scenario and achieve the expected result? Any help would be appreciated! This is part of a larger service I'm building. Has anyone else encountered this? Is there a better approach? I'm working in a CentOS environment. This is part of a larger mobile app I'm building. Thanks for your help in advance! This is for a microservice running on Windows 10. Thanks for any help you can provide!