SQL Results Separated by Commas

I'm kinda stuck in writing Stored Procedure. Here is the case. I have one table, below is the illustration

|   Name   |   Score   |

| A        | 10        |
| A        | 20        |
| A        | 30        |
| B        | 20        |
| B        | 50 开发者_StackOverflow       |

And i'm trying to get a result as follows from the Stored Procedure

|   Name   |   Scores  |

| A        | 10,20,30  |
| B        | 20,50     |

Is it possible to get such a result from a SQL Query or Stored Procedure ? How ?

---

Revised after considering comments:

I recommend reading Rob Farley's excellent blog post Handling special characters with FOR XML PATH(''). His solution will allow for string concatenation of your grouped ID's without concern for fields that might have special characters.

DECLARE @t TABLE (Name CHAR(1), Score INT) 
INSERT @t VALUES 
('A', 10),
('A', 20), 
('A', 30), 
('B', 20), 
('B', 50)   

SELECT
  STUFF(
    (SELECT ', ' + CONVERT(VARCHAR(10), Score)
     FROM @t 
     WHERE Name = t.Name
     ORDER BY Score
     FOR XML PATH(''),
     TYPE).value('(./text())[1]','varchar(max)'),
   1, 2, '') AS Score     
FROM @t t 
GROUP BY Name 

---

In this case COALESCE is your friend. I'm not a COALESCE expert, I just know it works so you may want to look it up if you want to dig deeper.

The snippet below will get you one row at a time, given the name to look for in @Name, I'd turn this into a function in SQL server, then call that function in your upper level SP somewhere, word of caution though: If you have NULL values in your result set, it will cause you to get incorrect results.

    DECLARE @Name varchar
    SET @Name = 'A'

    DECLARE @Row varchar(max)

    SELECT
        @Row = COALESCE(@Row + ', ','') + CAST(Score AS varchar)

    FROM
        sotest2

    WHERE
        name = @Name

    SELECT @Name,@Row

'sotest2' is the table name by the way, that was just what I called it in my db :-)

---

You can create a CLR user-defined aggregate:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable()]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls=true,
    IsInvariantToDuplicates=false,
    IsInvariantToOrder=false,
    MaxByteSize=8000)]
public class Concat : IBinarySerialize
{
    #region Private fields
    private string separator;
    private StringBuilder intermediateResult;
    #endregion

    #region IBinarySerialize members
    public void Read(BinaryReader r)
    {
        this.intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
    #endregion

    #region Aggregation contract methods
    public void Init()
    {
        this.separator = ", ";
        this.intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString pValue)
    {
        if (pValue.IsNull)
        {
            return;
        }

        if (this.intermediateResult.Length > 0)
        {
            this.intermediateResult.Append(this.separator);
        }
        this.intermediateResult.Append(pValue.Value);
    }

    public void Merge(Concat pOtherAggregate)
    {
        this.intermediateResult.Append(pOtherAggregate.intermediateResult);
    }

    public SqlString Terminate()
    {
        return this.intermediateResult.ToString();
    }
    #endregion
}

and use it in a query like any other aggregate function:

SELECT Name, dbo.Concat(Score) AS Scores
FROM dbo.Table
GROUP BY Name

The article A SQL CLR user-defined aggregate - notes on creating and debugging posted on my blog contains a detailed explanation of this code.

---

The quirky update method:

DECLARE @DistinctName TABLE
(
    Name VARCHAR(10) PRIMARY KEY
);
INSERT  @DistinctName (Name)
VALUES  ('A'),('B');
DECLARE @Test TABLE
(
    TestId INT IDENTITY(1,1) PRIMARY KEY
    ,Name VARCHAR(10) NOT NULL
    ,Score INT NOT NULL
    ,Result VARCHAR(1000) NOT NULL DEFAULT ''
);
INSERT  @Test (Name, Score)
VALUES  ('A',10),('A',20),('A',30),('B',40),('B',50);

DECLARE @OldName VARCHAR(10) = ''
        ,@IsNewGroup BIT = 1
        ,@Concat VARCHAR(1000);

WITH SourceCTE
AS
(
SELECT  TOP(1000) t.*
FROM    @Test t
ORDER BY t.Name
)
UPDATE  SourceCTE 
SET     @IsNewGroup = CASE WHEN @OldName <> Name THEN 1 ELSE 0 END
        ,@OldName = Name
        ,@Concat = Result = CASE WHEN @IsNewGroup = 1 THEN '' ELSE @Concat END + ',' + CAST(Score AS VARCHAR(10))
--OUTPUT    inserted.Name, inserted.TestId , inserted.Result

SELECT  dn.Name
        ,SUBSTRING(ca.Result,2,1000) Result
FROM    @DistinctName dn
CROSS APPLY
(
    SELECT  TOP(1) Result
    FROM    @Test t
    WHERE   t.Name = dn.Name 
    ORDER BY t.TestId DESC
) ca;

Mentions:

1. I have added a primary key (TestId) in @Test table.
2. Please read carefully this article about generating running totals using quirky update method to understand this method.