CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
roman m
roman m

Reputation: 26521

How to return multiple values in one column using 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

I'm using SQL Server 2005.

Upvotes: 51

Views: 166246

Answers (5)

CodeRedick
CodeRedick

Reputation: 7415

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'

Upvotes: 0

leoinfo
leoinfo

Reputation: 8195

/* 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    
*/

Upvotes: 21

Eric Z Beard
Eric Z Beard

Reputation: 38406

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.

Upvotes: 0

Tushar Maru
Tushar Maru

Reputation: 3425

DECLARE @Str varchar(500)

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

SELECT @Str

Upvotes: 1

Scott Nichols
Scott Nichols

Reputation: 6248

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

Upvotes: 61

Related Questions