tsql - How Coalesce works in sql server? -

create table test(names varchar(100) primary key )  insert test values('hugeman') insert test values('jack') insert test values('william') insert test values('kevin') insert test values('peter') 

query 1:

declare @sql varchar(100) select   @sql = coalesce(@sql+'+','')+names test order names-- object_id =object_id('temp')   print @sql 

this result hugeman+jack+kevin+peter+william

query 2

declare @sql varchar(100) select   @sql = coalesce(names+'+','')  test order names-- object_id =object_id('temp')   print @sql 

this results william+

as per documentation of coalesce, return first not null value. has result hugeman+. returns entire rows.

why query2 haven't done same ?

this not stricly connected coalesce.

try these selects:

declare @sql1 varchar(1000) select @sql1 = isnull(@sql1, '') + names test order names print @sql1  declare @sql2 varchar(1000) select @sql2 = names test order names print @sql2 

so happened? each record selected:

• in query 1 keep adding values @sql
• in query 2 reset @sql last name extracted

i don't understand want obtain select, better example of coalesce be:

create table test2(name varchar(100) primary key, telephone varchar(10), mobile varchar(10))  insert test2 values('hugeman', 1, 2) insert test2 values('jack', null, 3) insert test2 values('william', 4, null) insert test2 values('kevin', 5, 6) insert test2 values('peter', null, null)  select name,     coalesce(telephone, mobile) tel test2