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

Question

I have [UserAliases] (UserId, Alias) table 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 MSSQL Server 2005.

p.s. actual t-sql query would be appreciated :)

Answer 1

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

Answer 2

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.

Here's a link to a good explanation of COALESCE, which is a much cleaner way than using cursors:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Answer 3

There are some techniques to achieve this here: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Answer 4

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'

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

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

;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

Answer 10

DECLARE @Str varchar(500)

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

SELECT @Str