Sql-server – Aggregate SQL Rows into single column

sql serversql-server-2008

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

Best Answer

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;
Related Question