How to return multiple values in one column (T-SQL

问题:

I have a table UserAliases (UserId, Alias) with multiple aliases per user. I need to query it and return all aliases for a given user, the trick is to return them all in one column.

Example:

UserId/Alias  
1/MrX  
1/MrY  
1/MrA  
2/Abc  
2/Xyz

I want the query result in the following format:

UserId/Alias  
1/ MrX, MrY, MrA  
2/ Abc, Xyz

Thank you.

I\'m using SQL Server 2005.

p.s. actual T-SQL query would be appreciated :)

回答1:

You can use a function with COALESCE.

CREATE FUNCTION [dbo].[GetAliasesById]
(
    @userID int
)
RETURNS varchar(max)
AS
BEGIN
    declare @output varchar(max)
    select @output = COALESCE(@output + \', \', \'\') + alias
    from UserAliases
    where userid = @userID

    return @output
END

GO

SELECT UserID, dbo.GetAliasesByID(UserID)
FROM UserAliases
GROUP BY UserID

GO

回答2:

Well... I see that an answer was already accepted... but I think you should see another solutions anyway:

/* EXAMPLE */
DECLARE @UserAliases TABLE(UserId INT , Alias VARCHAR(10))
INSERT INTO @UserAliases (UserId,Alias) SELECT 1,\'MrX\'
     UNION ALL SELECT 1,\'MrY\' UNION ALL SELECT 1,\'MrA\'
     UNION ALL SELECT 2,\'Abc\' UNION ALL SELECT 2,\'Xyz\'

/* QUERY */
;WITH tmp AS ( SELECT DISTINCT UserId FROM @UserAliases )
SELECT 
    LEFT(tmp.UserId, 10) +
    \'/ \' +
    STUFF(
            (   SELECT \', \'+Alias 
                FROM @UserAliases 
                WHERE UserId = tmp.UserId 
                FOR XML PATH(\'\') 
            ) 
            , 1, 2, \'\'
        ) AS [UserId/Alias]
FROM tmp

/* -- OUTPUT
  UserId/Alias
  1/ MrX, MrY, MrA
  2/ Abc, Xyz    
*/

回答3:

Have a look at this thread already on StackOverflow, it conveniently gives you a T-SQL example.

回答4:

this is one of the fastest and simplest ways to do what you need without the need for a UDF: http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx

there\'s one other way using a numbers table that is faster for really large datasets but i don\'t think you\'ll need that.

回答5:

My boss wrote up an article on this way back 2003: Concatenation with COALESCE

回答6:

DECLARE @Str varchar(500)

SELECT @Str=COALESCE(@Str,\'\') + CAST(ID as varchar(10)) + \',\'
FROM dbo.fcUser

SELECT @Str

回答7:

You can either loop through the rows with a cursor and append to a field in a temp table, or you could use the COALESCE function to concatenate the fields.

回答8:

Sorry, read the question wrong the first time. You can do something like this:

declare @result varchar(max)

--must \"initialize\" result for this to work
select @result = \'\'

select @result = @result + alias
FROM aliases
WHERE username=\'Bob\'

回答9:

group_concat() sounds like what you\'re looking for.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

since you\'re on mssql, i just googled \"group_concat mssql\" and found a bunch of hits to recreate group_concat functionality. here\'s one of the hits i found:

http://www.stevenmapes.com/index.php?/archives/23-Recreating-MySQL-GROUP_CONCAT-In-MSSQL-Cross-Tab-Query.html