SELECT column values into a concatenated string (SQL)

Often there is a requirement to get column values in a table to a concatenated string. Say, we have e-mail ID as one column on a person table and want a comma separated list of all (or a subset) of e-mail ID’s.