Wednesday, August 4, 2010

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

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

No comments:

Post a Comment