Listing all products in 1 row SQL Server

I have a SQL Server database which has a table of products. Users can pick those products and they can put them in their cart, but because each organisation has their own prices they will be put into a ProductCartLine like this:

CartId	ProductId
1	1
1	2
1	3
2	3
2	4

I want to convert this to something like

CartId	ProductId
1	1,2,3
2	3,4

The last column doesn’t have to be 1 column, it can all be different columns, but everything needs to be in 1 row. I can’t quite figure out how to group everything together.

How do I do this in a T-SQL statement, or do I need to process this afterwards in code?

Answer

You want string aggregation:

select cartid,
    string_agg(productid, ',') within group(order by productid) as productids
from mytable
group by cartid