Say that you want to concatenate values from several records into a single string. The classic example is a list of categories where you want to present it to the user as Category Name followed by a comma separated list of all products for that category.
There are multiple solutions for this problem, but this one seems to be the most straight forward:
1: Select C.Name,
2: stuff((select ', ' + P.Name
3: from Product as P
4: where P.CategoryId = C.Id
5: for xml path('')),1,2,'') as ProductList
6: from Category as C