Many a time, SQL programmers are faced with a requirement to generate report-like result sets directly from a Transact SQL query. In most cases, the requirement arises from the fact that there neither sufficient tools nor in-house expertise to develop tools that can extract the data as a result set, and then massage the data in the desired display format.
Quite often folks are confused about the potential of breaking relational fundamentals such as the First Normal Form or the scalar nature of typed values. (Talking about 1NF violations in a language like SQL which lacks sufficient domain support, allows NULLs, and supports duplicates is somewhat ironic, to begin with, but that is a topic that requires detailed explanations.)
DECLARE @Names NVARCHAR(MAX)
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM tblLOBModel