SQL Query to get delimited results

By Jatin Prajapati

In this FAQ we will see how to write a SQL query to get delimited results.

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, ou
r 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

SQL Query to get delimited results  (590 Views)