sql concatenate string group by

Concatenate many rows into a single text string:

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT

  [ID],

  STUFF((

    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))

    FROM #YourTable

    WHERE (ID = Results.ID)

    FOR XML PATH (''))

  ,1,2,'') AS NameValues

FROM #YourTable Results

GROUP BY ID

DROP TABLE #YourTable