1

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.