Merge 2 rows values in SQL

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.



Ebook Download
View all
Learn
View all