Concatenating values in SQL

A relatively common need is concatenating a bunch values in a query. While cfoutput with "group" is one option, there are also some elegant sql options. Ones that do not involve temp tables or descending into cursor psychosis. There are different reasons for each, but if you are not aware of the sql options, Barney Boisvert sums them up quite nicely in this recent post at houseoffusion.com:

http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:59194