SQL 2005/2008 Query to retrieve a comma seperated single row from a table.

This query will help to combine all records in a single row result with comma seperation. I have taken both type string as well as integers.

-- Take a table variable

DECLARE @TBL as TABLE(VAL1 INT, VAL2 VARCHAR(10));

-- POPULATE THE VALUES

INSERT INTO @TBL VALUES(10,'A');

INSERT INTO @TBL VALUES(20,'B');

INSERT INTO @TBL VALUES(30,'C');

INSERT INTO @TBL VALUES(40,'D');

INSERT INTO @TBL VALUES(50,'E');

-- Show the values in tables

SELECT * FROM @TBL;



-- Declare variables

DECLARE @CommaValsInt AS VARCHAR(MAX);

DECLARE @CommaValsString AS VARCHAR(MAX);



-- Gets the sql query with Comma Seperated

SELECT 

    @CommaValsInt = COALESCE(@CommaValsInt + ',', '') + CAST(VAL1 AS VARCHAR(10)) 

    , @CommaValsString = COALESCE(@CommaValsString + ''',''', '''') + VAL2

FROM 

    @TBL;

-- Show the result

SELECT @CommaValsInt AS ResultForInt , (@CommaValsString + '''') ResultForString