Aggregate SQL Rows into single column

Question

I would like to aggregate results of a column into a single row using MS SQL.

I have create a sql fiddle which showcases the issue:

http://sqlfiddle.com/#!3/384d5/2

Basically I would like all 'Tag' results on the same line, grouped by the different Tags

So in the example results above, I would expect the results to be like so:

Id | Tag | OptionText
1 | IsMobile | Yes
1 | Mission | I am buying this, I am using this

The closest I have got is by using a STUFF function but I can't seem to get it correctly as it outputs all the OptionText values on each row:

select Id, Tag, STUFF(CAST((
 SELECT [text()] = ', ' + [OptionText]
 FROM testing
 FOR XML PATH(''), TYPE) AS
VARCHAR(max)), 1, 2, '') OptionText
 from testing
 group by Id, Tag

Here are the results for that query: http://sqlfiddle.com/#!3/384d5/5

Answer 1

You need to restrict your sub-query to look for the specific tags of the outer query.

I have aliased your outer table t1 and inner table t2.

 CREATE TABLE #testing
 (
 Id INT ,
 Tag NVARCHAR(250) ,
 OptionText NVARCHAR(250)
 );
 INSERT INTO #testing
 VALUES ( 1, 'IsMobile', 'Yes' );
 INSERT INTO #testing
 VALUES ( 1, 'Mission', 'I am using this' );
 INSERT INTO #testing
 VALUES ( 1, 'Mission', 'I am buying this' );
 SELECT
 Id ,
 Tag ,
 STUFF(CAST(( SELECT
 [text()] = ', ' + [OptionText]
 FROM
 #testing t2
 WHERE
 t2.Tag = t1.Tag -- Restrict rows returned in subquery
 FOR
 XML PATH('') ,
 TYPE
 ) AS VARCHAR(MAX)), 1, 2, '') OptionText
 FROM
 #testing t1
 GROUP BY
 Id ,
 Tag;