Create a table called Activities.
CREATE TABLE [dbo].[Activities](
[AcivityId] [int] IDENTITY(1,1) NOT NULL,
[FromUserId] [varchar](200) NULL,
[FromUserName] [varchar](200) NULL,
[ToUserId] [varchar](200) NULL,
[ToUserName] [varchar](200) NULL,
[Message] [varchar](500) NULL,
[Title] [varchar](500) NULL,
[Type] [varchar](50) NULL,
CONSTRAINT [PK_Activities] PRIMARY KEY CLUSTERED
(
[AcivityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
Inserted some records in the table as follows:
Insert into Activities(FromUserId,FromUserName,ToUserId,ToUserName,Message,Title,Type)
Values('puru','Puru','Posted a new','video article on 1st Jan','Article')
Insert into Activities(FromUserId,FromUserName,ToUserId,ToUserName,Message,Title,Type)
Values('puru','Puru','Posted a new','Video Article on 2nd Jan','Blog')
Create a new function:
CREATE FUNCTION [dbo].[GetAliasesByUserId]
(
@UserID varchar(100)
)
RETURNS Varchar(max)
AS
BEGIN
Declare @Output Varchar(max)
Select @Output = COALESCE(@output + ', ', '') + Title
From Activities
Where fromUserID = @UserID
return @output
END
GO
Fetch the record from the table Activities:
SELECT fromUserID As UserID, dbo.GetAliasesByUserId(fromUserID) As ClubbedTitle
FROM Activities WHere FromUserId='puru'
GROUP BY fromUserID
The output of the above select statement is
UserID Title
puru video article on 27 april, Video Article on 29 April
Thus, we can concatenate column values of multiple rows into a Single column in SQL server.