SQL- Concatenate data in Table

 
If you want to merge strings in different rows a table separated by a character (say comma[,]),
then you can do so easily with a simple SQL Trick without requiring any loop.
 
You just have to declare a varchar variable long enough to hold all data.
and write a select statement for the table. The following example illustrates this more.
 
First create a test table with some test data.
 
create table #tbl_test ( address varchar(50))
insert into #tbl_test values ( 'Lubhu')
insert into #tbl_test values ( 'Lalitpur')
insert into #tbl_test values ( 'Kathmandu')
 
Now, declare the variable and write select statement as below.
 
declare @Result varchar(8000)
set  @Result = ''
select  @Result = @Result + case when len(@Result)>0 then ',' else '' end + address
from #tbl_test
 
select @Result
 
You will be surprised by how it works.
The output will be Lubhu,Lalitpur,Kathmandu.
 
Remeber that, the variable should be long enough to hold all data otherwise it will lose excessive data.
Varchar variable cannot hold more that 8000 characters in a single variable.
Also, the separtion character is not compulsory or can be any. No obligation on that.