The hell with Distinct string aggregation in Sql or why I love Unique transformation

clickbait title ? Yes :grinning:

Case
MSSQL (even 2022) doesn’t support simple distinct string aggregation , instead requiring you to jump hoops by implementing CTE or subqueries making queries unnecessarily complex

We recently did massive cleanup/optimisation of our queries by exporting data with SRSS removing complex joins and using EDT UNIQUE feature to do distinct string aggregation with a simple click of a button (literally)

More technical , string_aggregation works as below but this is no unique and will duplicate values

SELECT
DepartmentID,
STRING_AGG(EmployeeName, ', ') AS EmployeeList
FROM Employees
GROUP BY DepartmentID;

What works is below, it might look simple but in complex queries it quickly adds up , specially when you want to aggregate multiple fields , you need to do separate CTE for ALL columns. All methods boil down to simple → Clunky

WITH DistinctEmployees AS (
SELECT DISTINCT DepartmentID, EmployeeName
FROM Employees
)
SELECT
DepartmentID,
STRING_AGG(EmployeeName, ', ') AS EmployeeList
FROM DistinctEmployees
GROUP BY DepartmentID;

1 Like

We possibly should have called Unique ‘Aggregate’ instead. But it is a bit late now. ;0)

1 Like