Open WinForms - Open Source Windows Forms and Controls

{} DailyCoding.com - 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?

Introduction

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":
SELECT * FROM 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

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?
Sponsors
 
  Tags: SQL Concatenate, SQL Server,

Facing issue while using? contact sa@openwinforms.com
 
 

Contact Site Admin: sa@openwinforms.com