Thursday, September 8, 2011

Display multiple rows values as single row value like comma separtae

SET NOCOUNT ON 
DECLARE @listValues VARCHAR(8000) 
DECLARE @delimeter VARCHAR(3) 
SET @delimeter = ' ; '

CREATE TABLE #Table1 (FirstName varchar(10)) 
INSERT #Table1 VALUES ('Michiel') 
INSERT #Table1 VALUES ('John') 
INSERT #Table1 VALUES ('Smith') 
INSERT #Table1 VALUES ('Peter') 
 select * from #Table1
SELECT @listValues = ISNULL(@listValues + @delimeter + CAST(FirstName AS VARCHAR(8000)), 
        CAST(FirstName AS VARCHAR(8000)) 
    ) 
    FROM #Table1
    ORDER BY FirstName 

DROP TABLE #Table1
SELECT list = @listValues

No comments:

Post a Comment