Tuesday, September 29, 2009 3:44 PM
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.
There are two ways of doing this or two that I know of – meaning simple enough.
1. The COALESCE method, which is probably the most common. This method works by tricking the coalesce function into doing something else, COALESCE is very similar to ISNULL except it can take in more than 2 arguments.
select @results = COALESCE(cast(ProductCategoryId as varchar)+',','')
from ProductCategory where ProductId = 24
2. Using the FOR XML PATH expression which works very well and its probably my preferred method because it takes order into consideration. If you use the FOR XML PATH without specifying the column name, you get concatenation – cool trick huh?
select cast(ProductCategoryId as varchar) + ',' from Productcategory
where ProductId = 24 for xml path('')
Have fun!!!