TSQL - rows to comma separated string

Sunday 01, February 2015   |   Post link

We often need to retrieve a column value from a table into a comma separated string. For example, lets say we have records like:

Roll	Name
1	Neel
2	Sid
3	Meena

We now want a list of names like Neel, Sid, Meena. This is really simple with a little bit of recursion. Here is the code:

-- Lets make a table and fill it with sample data
declare @Student table
(
	roll int primary key identity (1,1),
	name varchar(50) not null
);

insert into @Student (name) values('Neel'), ('Sid'), ('Mina');

-- The main logic
declare @names varchar(max) = '';
select @names = @names + case when @names = '' then name else ', ' + name end from @Student;
select @names;

It's possible to do the same thing using the COALESCE function.

-- Lets make a table and fill it with sample data
declare @Student table
(
	roll int primary key identity (1,1),
	name varchar(50) not null
);

insert into @Student (name) values('Neel'), ('Sid'), ('Mina');

-- The main logic
declare @names varchar(max);
select @names = coalesce(@names + ', ', '') + name from @Student
select @names

The output is Neel, Sid, Mina.
Note, when using coalesce, the starting value of @names is NULL.

Categories: TSQL (5)

Tags: TSQL(3)