Garth Wells has posted a sweet trick in MSSQL Syntax for creating a comma-delimited string from a single column query using only a single SELECT statement.  He uses COALESCE and what I'd call "SELECT-embedded variable concatenation" to work a clever and concise solution.