clickbait title ? Yes
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;