String concat on MS SQL for a group of rows

问题:

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?

回答1:

Since you haven't specified the DB I'm giving you two options:

MySql

With MySql you should simply use GROUP_CONCAT() aggregate function.

Microsoft SQL Server 2005+

Obviously the fastest way (no cursors, no coalesce...) of getting the same result on MS DB is by using FOR XML PATH('') that simply omits XML elements.

SELECT
    u.Name,
    c1.UserId,
    (
        SELECT c2.Code + ','
        FROM Codes c2
        WHERE c2.UserId = c1.UserId
        ORDER BY c2.code
        FOR XML PATH('')
    ) as Codes
FROM Codes c1
    JOIN Users u
    ON (u.Id = c1.UserId)
GROUP BY c1.UserId, u.Name

Other alternatives

Read this article, that explains all the possible ways of achieving this goal.

回答2:

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.