I learned this trick a year or so ago from the Philippine SQL Server User’s Group (PHISSUG) forums. It has its uses so I’m writing it down, somebody might find it useful.
For example, we have the following table:
CREATE TABLE #larins (
name VARCHAR(50)
)
We populate it using:
INSERT INTO #larins VALUES ('Dionne')
INSERT INTO #larins VALUES ('Joven')
INSERT INTO #larins VALUES ('Issie')
To get a list of all records:
SELECT * FROM #larins
Result:
name Dionne Joven Issie
But what if we want the names to be in one line? Before, I’d probably create a user-defined function to parse the records via a cursor. Turns out, there’s a much simpler way to do this:
DECLARE @names VARCHAR(8000)
SET @names = ''SELECT @names = @names + name + ‘, ‘
FROM #larinsSELECT @names
The result?
Dionne, Joven, Issie,
To remove the trailing comma would be left as an exercise to the reader… Just kidding. Modified query:
SELECT CASE WHEN RIGHT(RTRIM(@names), 1) = ',' THEN LEFT(RTRIM(@names), LEN(RTRIM(@names)) - 1) ELSE @names END AS 'results'
Voila! We get:
Dionne, Joven, Issie
Btw, anybody know where I can HTML-ify code? It just doesn’t look code-y enough. 
