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

Question

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?

If you just need need values for debugging, `min()` and `max()` are the easiest way to get two values. — Gordon Linoff, Dec 17 '13 at 13:50
http://stackoverflow.com/questions/17178039/accomplishing-mysqls-group-concat-in-sql-server — Darshan Mehta, Dec 17 '13 at 13:55
While I like [the eventual duplicate that is proposed](http://stackoverflow.com/a/545672/61305), I don't like that it adds a comma to the end, and takes the length of every output value just to remove that last comma. I think that's grossly inexpensive and far prefer adding a comma to the beginning - then STUFF makes it easy to remove it without ever having to count characters. — Aaron Bertrand, Dec 17 '13 at 13:57
Similar to: http://stackoverflow.com/questions/12559551/sql-server-equivalent-of-wm-concat-function — xQbert, Dec 17 '13 at 14:02

Aaron Bertrand · Accepted Answer · 2022-02-04T04:53:11.083

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,'')

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

1 Answers1