StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>for SQL Server 2017 and up use:</strong></p> <p>STRING_AGG()</p> <pre><code>set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,'CCC') insert into @YourTable VALUES (2,2,'B<&>B') insert into @YourTable VALUES (3,2,'AAA') insert into @YourTable VALUES (4,3,'<br>') insert into @YourTable VALUES (5,3,'A & Z') set nocount off SELECT t1.HeaderValue ,STUFF( (SELECT ', ' + t2.ChildValue FROM @YourTable t2 WHERE t1.HeaderValue=t2.HeaderValue ORDER BY t2.ChildValue FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,2, '' ) AS ChildValues FROM @YourTable t1 GROUP BY t1.HeaderValue SELECT HeaderValue, STRING_AGG(ChildValue,', ') FROM @YourTable GROUP BY HeaderValue </code></pre> <p>OUTPUT:</p> <pre><code>HeaderValue ----------- ------------- 1 CCC 2 B<&>B, AAA 3 <br>, A & Z (3 rows affected) </code></pre> <p><strong>for SQL Server 2005 and up to 2016, you need to do something like this:</strong></p> <pre><code>--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >" set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,'CCC') insert into @YourTable VALUES (2,2,'B<&>B') insert into @YourTable VALUES (3,2,'AAA') insert into @YourTable VALUES (4,3,'<br>') insert into @YourTable VALUES (5,3,'A & Z') set nocount off SELECT t1.HeaderValue ,STUFF( (SELECT ', ' + t2.ChildValue FROM @YourTable t2 WHERE t1.HeaderValue=t2.HeaderValue ORDER BY t2.ChildValue FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,2, '' ) AS ChildValues FROM @YourTable t1 GROUP BY t1.HeaderValue </code></pre> <p>OUTPUT:</p> <pre><code>HeaderValue ChildValues ----------- ------------------- 1 CCC 2 AAA, B<&>B 3 <br>, A & Z (3 row(s) affected) </code></pre> <p>Also, watch out, not all <code>FOR XML PATH</code> concatenations will properly handle XML special characters like my above example will.</p>

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