Compile single string from sql results [duplicate]

This question already has answers here: Closed 12 years ago. 开发者_运维问答

Possible Duplicate:

TSQL query to concatenate and remove common prefix

Hi

How does one compile a single string from the results of an SQL query.

E.g.

Table A
Column: Name
Row 0 : John
Row 1 : Sam
Row 2 : Kate

Resulting SQL query must return a single row with the result as a single string.

E.g.

"John, Sam, Kate"

Note: Using MS SQL 2005

---

This is what I usually use, just remove the table and add your own:

DECLARE @A
  TABLE ( Name VARCHAR(50) );

INSERT INTO @A ([Name]) VALUES ('John');
INSERT INTO @A ([Name]) VALUES ('Sam');
INSERT INTO @A ([Name]) VALUES ('Kate');

SELECT REPLACE((SELECT [Name] AS [data()]
  FROM @A
   FOR XML PATH('')
     ) , ' ', ', ') [Concatenated]

Should result in:

Concatenated
-------------------
John, Sam, Kate

---

GROUP_CONCAT

EDIT

Equivalent can be seen here

---

Use a cursor. Something like this (from here) should get you started:

DECLARE @tbl TABLE (id INT PRIMARY KEY, list VARCHAR(8000))

SET NOCOUNT ON

DECLARE @c INT, @p VARCHAR(8000), @cNext INT, @pNext VARCHAR(40)

   DECLARE c CURSOR FOR 

   SELECT CategoryId, ProductName 

     FROM Northwind..Products 

    ORDER BY CategoryId, ProductName ;

   OPEN c ;

   FETCH NEXT FROM c INTO @cNext, @pNext ;

   SET @c = @cNext ;

   WHILE @@FETCH_STATUS = 0 BEGIN 

        IF @cNext > @c BEGIN 

             INSERT @tbl SELECT @c, @p ;

             SELECT @p = @PNext, @c = @cNext ;

        END ELSE 

             SET @p = COALESCE(@p + ',', SPACE(0)) + @pNext ;

        FETCH NEXT FROM c INTO @cNext, @pNext 

   END 

   INSERT @tbl SELECT @c, @p ;

   CLOSE c ;

DEALLOCATE c ;

SELECT * FROM @tbl ;

---

Quick and dirty way to do it, not a lot of error checking, but it works...

DECLARE @theList VARCHAR(2000)
SET @theList =''

SELECT @TheList=@theList+[name]+','
FROM <tableName> WHERE [name] is not null

IF @@rowcount > 0
    SET @TheList=left(@TheList,len(@TheList)-1)

PRINT @theList