String concat on MS SQL for a group of rows

Question

Lets say I have 2 tables:

1 with users and another one which keeps record of which users used what codes.

Users
-----
Id, Name
1, 'John'
2, 'Doe'

Codes
------
Id, UserId, code
1, 1, 145
2, 1, 187
3, 2, 251

Now I want to pull a query that results he following

Name, UsedCodes
'John', '145,187'
'Doe', '251'

How can this be done with a query or stored procedure?

Answer 1

For SQL Server as a really quick and dirty you could use a SQL function and a cursor. I would not really recommend this for high usage and I'll be really embarassed when someone points out a much easier example that doesn't need a function let alone a cursor.

SELECT
 t1.Name,
 StringDelimitCodes(t1.ID) as 'UsedCodes'
FROM
 users t1

And the function would be something like

function StringDelimitCodes(@ID INT) VARCHAR(255)
AS
BEGIN
  DECLARE CURSOR myCur
   AS SELECT Code FROM Codes WHERE ID UserID = @ID
  OPEN myCur
  DECLARE @string VARCHAR(255)
  FETCH @MyCode = Code FROM myCur
  WHILE @@FetchStatus ==0  
  BEGIN
    IF(@string <> '') 
    BEGIN
      SELECT @String = @String + ','
    END
      SELECT @String = @String + CAST(@CODE AS VARCHAR(10))
    FETCH @MyCode = Code FROM myCur
  END
  CLOSE myCur
  DEALLOCATE myCUR
  RETURN @string 
END

EDIT: Sorry for any SQL Syntax errors, don't have SQL installed here to validate, etc. so done from memory.