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