Flatten SQL Server Table to a string

I have a table like:

ID  | Value
----------------
 1  | One
 2  | Two
 3  | Three

What I need to do is create a single string from these values, in the format:

'1: One, 2: Two, 3: Three'

I know how to do this using cursors, but it will be used as a column i开发者_如何学Pythonn a view, so it's not really a performant option. Any pointers?

WITH T(ID,Value) AS
(
SELECT 1, 'One' UNION ALL
SELECT 2, 'Two' UNION ALL
SELECT 3, 'Three' 
)

SELECT  STUFF(
        (SELECT ', ' + CAST(ID as varchar(11)) + ': ' + Value
         FROM T
         FOR XML PATH (''))
     , 1, 2, '')

Have a look at something like

DECLARE @Table TABLE(
        ID INT,
        Value VARCHAR(20)
)
INSERT INTO @Table SELECT 1,'One'
INSERT INTO @Table SELECT 2,'Two'
INSERT INTO @Table SELECT 3,'Three'

SELECT  STUFF(
                (
                    SELECT  ', ' + CAST(ID AS VARCHAR(MAX)) + ': ' + Value
                    FROM    @Table
                    FOR XML PATH(''), TYPE 
               ).value('.','varchar(max)') 
               ,1,2, '' 
          )

SELECT STUFF((
    SELECT ' ' + CAST(ID AS VARCHAR(2)) + ': '+ Value
    FROM   dbo.Table
    FOR    XML PATH('')
    ), 1, 1, ''
  ) As concatenated_string

DECLARE @ans VARCHAR(max)

SET @ans=''

SELECT @ans = @ans + str(id)+':'+value FROM table

SELECT @ans

Change the max to 8000 if your version of SQL doesn't support it

I would simply not do this in the database if at all possible. It's not designed for formatting data in a certain way; let the calling application handle that.

In C# (assuming data is an instance of SqlDataReader):

var l = new List<string>();
while (reader.Read())
    l.Add(string.Format("{0}: {1}", reader[0], reader[1]));
var s = string.Join(", ", l.ToArray());