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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

DECLARE  Str varchar 500   SELECT  Str COALESCE  Str       CAST ID as varchar 10         FROM dbo fcUser  SELECT  Str

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql