How to concatenate from multiple rows, then group and count, in SQL Server

0
votes

I am trying to get from this

tPeople
Name
Alice
Bob

tAnimals
Name Animal
Alice Cat
Alice Cat
Bob Horse
Bob Dog

To this

Alice "Cat*2"
Bob "Horse, Dog"

So far I have an intermediate table grouping and counting animals

tGrouped Name Animal Tally
Alice Cat 2
Bob Dog 1
Bob Horse 1

And if I run

SELECT Name, Animals=STUFF((
    SELECT N', '+animal+'*'+tally FROM tgrouped as g
    WHERE g.Name = p.Name
    FOR XML PATH(''), TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, N'')
FROM tpeople as p

I get
Name Animals
Alice Cat*2
Bob Dog*1, Horse*1

Is there any way to skip the intermediate table? And any way to not show the tally when it is 1?

2
sqlsql-serverstringconcatenation
Why "Cat*2" but not "Dog*1"? Are there other times you would have different notations? - Larnu
Larnu, the specification I was given asks that anything repeated gets a count, but if there's only one then it is shown without a count. I think the data requester is going to further process it, assigning money values to dogs etc and the * will really act as a delimiter, - Steeev

2 Answers

1
votes

You don't need an intermediate table. You can just do the calculation in the query. For the second you can just adjust your logic:

WITH grouped as (
      SELECT a.name, a.animal, COUNT(*) as cnt
      FROM tAnimals a
      GROUP BY a.name, a.animal
     )
SELECT Name,
       STUFF((SELECT (CASE WHEN cnt = 1 THEN N', ' + animal
                           ELSE CONCAT(N', ', animal, '*',  cnt)
                      END)
              FROM grouped g
              WHERE p.Name = g.Name
              FOR XML PATH(''), TYPE
             ).value(N'.[1]', N'varchar(max)'
                    ), 1, 2, N''
            ) as animals
FROM tpeople p;
0
votes

Similar approach to Gordon, but without the CTE:

CREATE TABLE dbo.tPeople ([Name] nvarchar(10));
CREATE TABLE dbo.tAnimal ([Name] nvarchar(10),
                          Animal varchar(10));

INSERT INTO  dbo.tPeople ([Name])
VALUES('Alice'),('Bob');

INSERT INTO dbo.tAnimal ([Name],
                         Animal)
VALUES('Alice','Cat'),
      ('Alice','Cat'),
      ('Bob', 'Dog'),
      ('Bob', 'Horse');

GO

SELECT P.[Name] + ' "' +
       STUFF((SELECT ', ' + CONCAT(A.Animal, '*' + CONVERT(varchar(3),NULLIF(COUNT(A.Animal),1)))
              FROM dbo.tAnimal A
              WHERE A.[Name] = P.[Name]
              GROUP BY A.Animal
              FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,2,'') + '"'
FROM dbo.tPeople P;



GO
DROP TABLE dbo.tPeople;
DROP TABLE dbo.tAnimal;