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

Question

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 :)

Answer 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 

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