sql server - How does sql returns all rows when concating declared column and defining it? -

how possible in sql?

declare @liststr varchar(max) ;with comma (name) (     select 'a'      union     select 'b'     union     select 'c'     union     select 'd' ) select @liststr = case when  @liststr+',' null '' else @liststr+',' end + name  comma  select @liststr result 

the above query retuns following result

+---------+ | result  | +---------+ | a,b,c,d | +---------+ 

where if remove case statement.

declare @liststr varchar(max) ;with comma (name) (     select 'a'      union     select 'b'     union     select 'c'     union     select 'd' ) select @liststr = @liststr+',' + name comma select @liststr result 

the result is

+--------+ | result | +--------+ | null   | +--------+ 

not @ all. when sql server concatenates string null value, value null.

if set value empty string first, both return same values:

declare @liststr varchar(max); set @liststr = ''; 

two notes:

• use union all rather union, unless explicitly want incur overhead of removing duplicates.
• when running query, results in arbitrary order. if want particular order, use order by.

note erland sommarskog says method of combining strings unsupported. although recommends cursor in particular answer, use xml.