Here's an interesting way of contencating values in one column. It makes use of the new XML capabilities.
use master
select column_name as col1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'spt_values'
select (select column_name as col1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'spt_values' for xml raw, elements, type).query('
for $col1 in (row/col1)
return concat($col1, ",")')
Order can be forced by using
'for $col1 in (row/col1)
order by $col1 ascending
return concat($col1, ",")'
For descending order change ascending to descending.