Open WinForms - Open Source Windows Forms and Controls

{} - Tips and tricks - C#, ASP.NET
 Subscribe in a reader

Home    Controls    Articles    Contact
  Concatenate colums's data in sql query
Post comments    Need help?


May time we need column?s data into a single row from a database table. This is easy to do if we are doing this at application level. However this could also be done in a sql query. Let?s take an example.

Suppose we have a table for "Fruits":

FruitId      FruitName
1001         Apple
1002         Orange
1003         Mango
1004         Banana
1005         Grape

Now my need is to show all fruits in a single row separated by comma (,). One commonly used approach is to fetch all rows and concatenate data into out application
while (dr.Read())
  fruitNames += dr["FruitName"] + ",";

We can do the same thing in a Sql Server Query also

DECLARE @fruitNames VARCHAR(8000) 
SELECT @fruitNames = COALESCE(@fruitNames + ', ', '') + FruitName FROM Fruits
SELECT FruitNames = @fruitNames

Apple, Orange, Mango, Banana, Grape

The COALESCE function is used to ensure that there is no comma (,) after the last FruitName.
Post comments    Need help?
  Tags: SQL Concatenate, SQL Server,

Facing issue while using? contact

Contact Site Admin: