Concatenating multiple rows into single column in SQL Server

Sometimes you need to return a single column that contains values that are comma separated or pipe separated or what ever floats your boat from a multiple row result set, a perfect example is returning the category id’s for a product that could be in one of more categories but you don’t want to return multiple rows with duplicate information.