How to restore Facebook-like people using SQL Server?

Question

Here, in my application, there are problems and a list of people who encountered them. Today I retrieve the names of all people using the STUFF function, as shown 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 many people are faced with the same problem, the peopleWhoFaced field becomes huge. How to get something like Person 1, Person 2 and 36 more faced the problem ? I know that I could do this at the API level, but I try to avoid this and keep the API code clean.

How could I do this? Do I need a cursor or something like that?

Thanks in advance.

Answer 1

Add 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