display records in a comma seperated values in the final row, SQL Server

Format

9XSA

ASX9

How can I display the records seperated by comma seperated values in the final row ? using a 'SELECT' query ?

the result should be

Format

9XSA

ASX9,9XSA

if there are three recor开发者_高级运维ds for instance, then

Format

9XSA

7BSF
ASX9,9XSA,7BSF

DECLARE @CSV VARCHAR(MAX)
SET @CSV = ''

SELECT @CSV = @CSV + ',' + Field1 FROM Table1

SET @CSV = RIGHT(@CSV, LEN(@CSV)-1)

SELECT Field1 FROM Table1
UNION
SELECT @CSV