Concatenate strings from a column into a single row?

CREATE DATABASE Test1
Go
Use Test1
set nocount on
CREATE TABLE dbo.Test2
(
Col1 int identity,
Col2 varchar (5) default('aaaaa')
)
declare @loop
int set @loop = 1
while @loop <= 100
begin
insert into Test2 default values
set @loop = @loop + 1
end


Now you had create a Database Test1 and a Table Test2 with 100 records

Now concatenate this to a single string

DECLARE @ConcatString varchar(8000)
SELECT @ConcatString = COALESCE(@ConcatString + ',','' ) + CAST(Col1 as varchar(12))
FROM Test2
SELECT @ConcatString

You can find the same result by using ISNULL

DECLARE @ConcatString varchar(8000)
SELECT @ConcatString = ISNULL(@ConcatString + ',','' ) + CAST(Col1 as varchar(12))FROM Test2
SELECT @ConcatString


Enjoy .. .. better performance using this instead of using CURSOR.