Combing values of a column on multiple rows.?

Question

I'm writing a query that return multiple rows that are identical in all ways except for one column. What I want to do is combine those rows into one row and join that one column with commas.

So if my query currently returns something like this:

Animal    Name     Toy
Cat       Kitty    Mouse
Cat       Kitty    CatNip
Dog       Rover    Bone
Dog       Rover    Chew Toy

I want it to return this instead:

Animal Name Toy
Cat    Kitty Mouse, CatNip
Dog    Rover Bone, Chewtoy

Does that make sense?

Any suggestions?

Answer 1

DECLARE @a TABLE
(
   Animal VARCHAR(32), 
   Name   VARCHAR(32), 
   Toy    VARCHAR(32)
);

INSERT @a SELECT 'Cat','Kitty','Mouse';
INSERT @a SELECT 'Cat','Kitty','CatNip';
INSERT @a SELECT 'Dog','Rover','Bone';
INSERT @a SELECT 'Dog','Rover','Chew Toy';

SELECT Animal, Name, Toy = STUFF((SELECT ', ' + Toy
    FROM @a AS a2
    WHERE a2.Animal = a.Animal AND a2.Name = a.Name
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'),
    1,2,'')
FROM @a AS a
GROUP BY Animal, Name;

In SQL Server 2017+, this has become much easier:

SELECT Animal, Name, Toy = STRING_AGG(Toy, ', ')
  FROM @a
  GROUP BY Animal, Name;

• Example db<>fiddle