Sql aggregate function to obtain a list

Question

Hei!

How can I create an aggregate function to obtain a list of the aggregate values.

given :

key     value
Andrei  1
Andrei  2
Andrei  3
Mihai   4
Mihai   5
Mihai   6

I want

key list
Andrei 1,2,3
Mihai 4,5,6

Answer 1

MS SQL solution: [Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

SELECT 
key
, STUFF((SELECT ',' + CAST([value] AS nvarchar(max)) FROM <#Table#> WHERE (key = Results.key) FOR XML PATH ('')),1,2,'') AS NameValues
FROM <#Table#> Results
GROUP BY key