👀 Views: 2 💬 Answers: 1 📅 Created: 2025-06-06

sql-server string-agg null-values

I'm working on a report generation feature using SQL Server 2017, and I've encountered some unexpected behavior with the `STRING_AGG` function when handling `NULL` values in grouped queries. I have a table called `SalesData` that looks like this: ```sql CREATE TABLE SalesData ( SalesPersonID INT, SaleAmount DECIMAL(18, 2), SaleDate DATE ); INSERT INTO SalesData (SalesPersonID, SaleAmount, SaleDate) VALUES (1, 100.00, '2023-01-01'), (1, NULL, '2023-01-02'), (2, 250.00, '2023-01-01'), (2, 300.00, '2023-01-05'); ``` I want to generate a summary report that concatenates the `SaleDate` values for each `SalesPersonID`, but I noticed that the `NULL` values are causing issues. My current query looks like this: ```sql SELECT SalesPersonID, STRING_AGG(SaleDate, ', ') AS SaleDates FROM SalesData GROUP BY SalesPersonID; ``` This query returns the following results: | SalesPersonID | SaleDates | |----------------|-----------------------| | 1 | 2023-01-01, NULL | | 2 | 2023-01-01, 2023-01-05| I expected the `NULL` value for `SalesPersonID 1` to be ignored in the `STRING_AGG` result. However, it's still being included in the output as `NULL`. I've tried wrapping `SaleDate` with `ISNULL` and filtering out `NULL` values in the `WHERE` clause, but that doesn't produce the desired result: ```sql SELECT SalesPersonID, STRING_AGG(ISNULL(SaleDate, ''), ', ') AS SaleDates FROM SalesData WHERE SaleDate IS NOT NULL GROUP BY SalesPersonID; ``` This doesn't work either, as it simply excludes `SalesPersonID 1` from the results entirely. Is there a way to achieve the desired output, ensuring `NULL` values are ignored while still retrieving all relevant `SalesPersonID`s? Any suggestions or best practices for handling this scenario would be greatly appreciated.