Create a table called <UserAliases>

CREATE TABLE UserAliases
(
UserID int,
alias varchar(50)
)
GO

Insert some records into table <UserAliases>.

insert into UserAliases(UserID,Alias)
values(1,'Aliases1')
insert into UserAliases(UserID,Alias)
values(1,'Aliases2')
insert into UserAliases(UserID,Alias)
values(2,'Aliases3')
insert into UserAliases(UserID,Alias)
values(3,'Aliases4')
insert into UserAliases(UserID,Alias)
values(4,'Aliases5')
insert into UserAliases(UserID,Alias)
values(2,'Aliases6')
insert into UserAliases(UserID,Alias)
values(3,'Aliases7')
GO

Create a function :

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) As AliasName
FROM UserAliases
GROUP BY UserID

The result looks like this

UserID AliasesName
1 Aliases1, Aliases2
2 Aliases3, Aliases6
3 Aliases4, Aliases7
4 Aliases5

Happy coding.

Comments

Join the conversation! Your thoughts help the community grow.