While developing data driven applications, many of us need to get delimited results from SQL database. So to get this we can use coalesce function provided in SQL SERVER 2005 and later. This function checks for the null
values provided as paramteres to it and returns the first non null value. So we will this function to get our delimited results. For this FAQ I've used Northwind database and Products table.
declare @products varchar(2000)
Select @products=coalesce(@products + ',','') + ProductName from Products
Select @products
So, our results wil be as shown below.
Alice Mutton,Aniseed Syrup,Boston Crab Meat,Camembert Pierrot,Carnarvon Tigers,Chai,Chang,Chartreuse
verte,Chef Anton's Cajun Seasoning,Chef Anton's Gumbo Mix,Chocolade,Côte de Blaye,Escargots
de Bourgogne,Filo Mix,Flotemysost,Geitost,Genen Shouyu,Gnocchi di nonna Alice,Gorgonzola
Telino,Grandma's Boysenberry Spread,Gravad lax,Guaraná Fantástica,Gudbrandsdalsost,Gula
Malacca,Gumbär Gummibärchen,Gustaf's Knäckebröd,Ikura,Inlagd Sill,Ipoh Coffee,Jack's
New England Clam Chowder,Konbu,Lakkalikööri,Laughing Lumberjack Lager,Longlife
Tofu,Louisiana Fiery Hot Pepper Sauce,Louisiana Hot Spiced Okra,Manjimup Dried
Apples,Mascarpone Fabioli,Maxilaku,Mishi Kobe Niku,Mozzarella di Giovanni,Nord-Ost
Matjeshering,Northwoods Cranberry Sauce,NuNuCa Nuß-Nougat-Creme,Original Frankfurter
grüne Soße,Outback Lager,Pâté chinois,Pavlova,Perth Pasties,Queso Cabrales,Queso
Manchego La Pastora,Raclette Courdavault,Ravioli Angelo,Rhönbräu Klosterbier,Röd
Kaviar,Rogede sild,Rössle Sauerkraut,Sasquatch Ale,Schoggi Schokolade,Scottish
Longbreads,Singaporean Hokkien Fried Mee,Sir Rodney's Marmalade,Sir Rodney's
Scones,Sirop d'érable,Spegesild,Steeleye Stout,Tarte au sucre,Teatime Chocolate
Biscuits,Thüringer Rostbratwurst,Tofu,Tourtière,Tunnbröd,Uncle Bob's Organic
Dried Pears,Valkoinen suklaa,Vegie-spread,Wimmers gute Semmelknödel,Zaanse koeken