Displaying a delimited string for values that are not in the GROUP BY clause

When I have an SQL query with a GROUP BY clause, It is often very useful to see some of the un-grouped values for easier debugging.

My question is, how can I select a string that will be composed of the un-grouped values.
For example, in the following code:

SELECT  t2.ID 
        --,t1.Id    -- < how can I display this as a comma seperated string   
FROM t1 
     INNER JOIN t2 on t1.t2ID = t2.ID
GROUP BY t2.ID

I would like to have a way to select a string with t1.Id‘s for each grouped record (e.g. "42, 13, 18"…).
How can I achieve that?

Answer

Assuming these are integer values, you can use a naked XML PATH transform to handle group concatenation for you (and this even supports predictable and well-defined order, unlike all other group concatenation methods – which have undefined behavior).

DECLARE @t2 TABLE(ID INT);
DECLARE @t1 TABLE(ID INT IDENTITY(1,1),t2ID INT);

INSERT @t2(ID) VALUES(1),(2),(3);
INSERT @t1(t2ID) VALUES(1),(1),(1),(2);

SELECT t2.ID, t2IDs = STUFF((
  SELECT ',' + CONVERT(VARCHAR(11), t1.ID)
  FROM @t1 AS t1 WHERE t1.t2ID = t2.ID
  ORDER BY t1.ID
  FOR XML PATH('')),1,1,'')
FROM @t2 AS t2;

Results:

ID    t2IDs
----  -----
1     1,2,3
2     4
3     NULL

Note that you don’t need ID in the GROUP BY clause, because you’re no longer needing to filter out duplicates matched by virtue of the JOIN. Of course this assumes your column is named appropriately – if that column has duplicates with no JOIN involved at all, then it has a terribly misleading name. A column named ID should uniquely identify a row (but even better would be to call it what it is, and name it the same throughout the model, e.g. CustomerID, OrderID, PatientID, etc).

If you’re dealing with strings, you need to account for cases where the string may contain XML-unsafe characters (e.g. <). In those cases, this is the method I’ve always used:

FOR XML PATH(''), TYPE).value(N'./text()[1]',N'nvarchar(max)'),1,1,'')