StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Assuming these are integer values, you can use a naked <code>XML PATH</code> 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).</p> <pre><code>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; </code></pre> <p>Results:</p> <pre class="lang-none prettyprint-override"><code>ID t2IDs ---- ----- 1 1,2,3 2 4 3 NULL </code></pre> <p>Note that you don't need <code>ID</code> in the <code>GROUP BY</code> clause, because you're no longer needing to filter out duplicates matched by virtue of the <code>JOIN</code>. Of course this assumes your column is named appropriately - if that column has duplicates with no <code>JOIN</code> involved at all, then it has a terribly misleading name. A column named <code>ID</code> 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. <code>CustomerID</code>, <code>OrderID</code>, <code>PatientID</code>, etc).</p> <p>If you're dealing with strings, you need to account for cases where the string may contain XML-unsafe characters (e.g. <code><</code>). In those cases, this is the method I've always used:</p> <pre><code>FOR XML PATH, TYPE).value('.[1]','nvarchar(max)'),1,1,'') </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload