How to retrieve Facebook-like people counter with SQL Server?

Question

Here in my app I have problems, and a list of people who faced them. Today I'm retrieving all people names through STUFF function like the sample below:

select problem.*, 
(
    STUFF
    (
        (
            SELECT TOP(3)', ' + person.name
            FROM 
                problem_person
                LEFT JOIN person ON problem_person.personId = person.Id
            WHERE 
                problem_person.problemId = problem.Id
                order by person.name
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
    )
) as peopleWhoFaced from problem

However, when a lot of people face the same problem, peopleWhoFaced field gets huge. How to retrieve something like Person 1, Person 2 and 36 more faced the problem ? I know that I could do this at API-level, but I'm trying to avoid doing so, and keep API code clean.

How could I accomplish that? Will I need a cursor or something like that?

Thank you in advance.

Answer 1

Append COUNT(*) - 3:

DECLARE @count int = (SELECT COUNT(*) - 3 
                      FROM problem
                      LEFT JOIN person 
                      ON problem.problemId = person.problemId
                      INNER JOIN
                         problem_person
                      ON problem_person.personId = person.Id)


select problem.*, 
(
    STUFF
    (
        (
            SELECT TOP(3)', ' + person.name
            FROM 
                problem_person
                LEFT JOIN person ON problem_person.personId = person.Id
            WHERE 
                problem_person.problemId = problem.Id
                order by person.name
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
    ) + CASE WHEN @count > 0 THEN N' and ' + CAST(@count as NVARCHAR(20)) + N' more faced the problem' ELSE N'' END
) as peopleWhoFaced from problem