SQL - Select - Concatenation

Sometimes you want to concatinate data from multiple records into one field. For example a list of states could be

Alabama
Alaska
Arizona
:

or it could be

Alabama,Alaska,Arizona...

This second output is an example of concatenation

Suppose you wanted a list of employees who are 42 years old. You could have a sql statement similar to the following:

select ','+empUserName 
from employees 
where age = 42

you might receive a list similar to the following

AgxxxxxAm
AnxxxL
ZarxxxraR
ZiexxxnE

But suppose you wanted the list in one field, comma seperated. Then you could use a combination of the for xml path(‘’) operator (to perform concatination services) and the Stuff function (to trim off leading comma).

select stuff(
	( select ','+empUserName 
		from employees 
		where cdEmpStatus in ('98') 
		and cdEmpType='CON'
		for xml path('')
		-- ,AgxxxxxAm,AnxxxL,ZarxxxraR,ZiexxxnE
	),1,1,'') x

This will give you an output similar to the following

AgxxxxxAm,AnxxxL,ZarxxxraR,ZiexxxnE