👀 Views: 60 💬 Answers: 1 📅 Created: 2025-06-24

sql-server string-agg null-values sql

I've been banging my head against this for hours. I'm trying to use the `STRING_AGG()` function in SQL Server 2017 to concatenate values from a column while grouping by another column. However, I've run into a question where it seems to ignore NULL values but doesn't return the expected output when NULLs are present. Here’s a simplified version of my query: ```sql SELECT Department, STRING_AGG(EmployeeName, ', ') AS EmployeeList FROM Employees GROUP BY Department; ``` In my `Employees` table, some departments have employees with NULL `EmployeeName` values. I expected the output to simply skip these NULLs, but I noticed that for some departments, the result set is returning NULL instead of the concatenated string of employee names. For example, if the `Sales` department has employees named 'Alice', 'Bob', and one NULL, I would expect `EmployeeList` to return 'Alice, Bob'. Instead, I'm getting NULL for departments that include at least one NULL value in their employee names. I’ve tried using a `WHERE` clause to filter out NULL names: ```sql SELECT Department, STRING_AGG(EmployeeName, ', ') AS EmployeeList FROM Employees WHERE EmployeeName IS NOT NULL GROUP BY Department; ``` However, this returns only departments with non-NULL employee names, which is not what I want. I want to include the department in the result even if all its employees have NULL names. Is there a way to modify my query so that departments are still included in the final result with a proper concatenation of non-NULL employee names? Any help would be appreciated! This is part of a larger application I'm building. Am I missing something obvious?