51

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
roman m
  • 26,012
  • 31
  • 101
  • 133
  • 1
    Related question (for others with the same problem): http://stackoverflow.com/questions/102317/how-to-get-multiple-records-against-one-record-based-on-relation – Eduardo Molteni Jan 29 '09 at 14:02
  • I am way too late but check this out - [the cleanest way yet](https://stackoverflow.com/a/276949/1270970) – Mārtiņš Radiņš Jun 09 '17 at 09:39
  • 1
    SqlServer 2017 now has [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) that aggregates multiple strings into one using a given separator. – John Aug 31 '17 at 11:26

6 Answers6

61

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
Scott Nichols
  • 6,108
  • 4
  • 28
  • 23
  • In this case if i use order by case in UserAliases table it is possible for i try but not success if another way to use "order by alias" in select statement any solution .. Please ... – Sagar Rawal Sep 25 '14 at 07:54
  • I know this is old, but how could we make it default to ORDER BY ascending order?, it seems to always come as descending – IeeTeY Apr 19 '23 at 23:24
21

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    
*/
leoinfo
  • 7,860
  • 8
  • 36
  • 48
1
DECLARE @Str varchar(500)

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

SELECT @Str
John Saunders
  • 160,644
  • 26
  • 247
  • 397
Tushar Maru
  • 3,347
  • 10
  • 34
  • 53
0

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

gehsekky
  • 3,187
  • 2
  • 21
  • 14
0

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.

user229044
  • 232,980
  • 40
  • 330
  • 338
Eric Z Beard
  • 37,669
  • 27
  • 100
  • 145
0

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'
Sabyasachi Mishra
  • 1,677
  • 2
  • 31
  • 49
CodeRedick
  • 7,346
  • 7
  • 46
  • 72