Sql server: limit string_agg result

Question

I have the following query (showing for each customer the list of users):

select cu.customer_id , STRING_AGG(u.first_name + ' ' + u.last_name , ',') as users   
from customer_user cu join user u on cu.user_id = u.id   
where ... 
group by cu.customer_id

How can I limit the string_agg function to aggregate only 10 elements for each group?

Answer 1

You may try to number the rows:

SELECT customer_id , STRING_AGG(first_name + ' ' + last_name , ',') AS users   
FROM (
   SELECT 
      cu.customer_id, u.first_name, u.last_name,
      ROW_NUMBER() OVER (PARTITION BY cu.customer_id ORDER BY (SELECT NULL)) AS rn
   FROM customer_user cu 
   JOIN user u ON cu.user_id = u.id
   -- WHERE ...
) t
WHERE rn <= 10
GROUP BY customer_id