Hi Team,

How to put Modifiedby User columns values from multiple rows for the sameid in comma separated in SSRS Report.

For example in the below table we have requestid 501 had been modified by many users... We want to generate report like below in SSRS

RequestID : 501

Request Name : OS Installation

Initial Request By :User1

RequestModifiedBy : User2,User3,User4

ProjectID:1

how to do this...Please help... Like RequestModifiedBy we have many columns...

CREATE TABLE [dbo].[RequestDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RequestID] [int] NOT NULL,
[RequestName] [varchar](50) NOT NULL,
[RequestedBy] [varchar](50) NOT NULL,
[RequestModifiedBy] [varchar](50) NOT NULL,
[ProjectId] [int] NOT NULL,
)

GO

SET IDENTITY_INSERT [dbo].[RequestDetail] ON

GO
INSERT [dbo].[RequestDetail] ([ID], [RequestID], [RequestName], [RequestedBy], [RequestModifiedBy], [ProjectId]) VALUES (1, 501, N'OS Installation', N'User1', N'User2', 1)
GO
INSERT [dbo].[RequestDetail] ([ID], [RequestID], [RequestName], [RequestedBy], [RequestModifiedBy], [ProjectId]) VALUES (2, 501, N'OS Installation', N'User1', N'User3', 1)
GO
INSERT [dbo].[RequestDetail] ([ID], [RequestID], [RequestName], [RequestedBy], [RequestModifiedBy], [ProjectId]) VALUES (3, 501, N'OS Installation', N'User1', N'User4', 1)
GO
INSERT [dbo].[RequestDetail] ([ID], [RequestID], [RequestName], [RequestedBy], [RequestModifiedBy], [ProjectId]) VALUES (4, 502, N'Driver Installation', N'User5', N'User74', 3)
GO
INSERT [dbo].[RequestDetail] ([ID], [RequestID], [RequestName], [RequestedBy], [RequestModifiedBy], [ProjectId]) VALUES (5, 502, N'Driver Installation', N'User5', N'User81', 3)
GO
INSERT [dbo].[RequestDetail] ([ID], [RequestID], [RequestName], [RequestedBy], [RequestModifiedBy], [ProjectId]) VALUES (6, 502, N'Driver Installation', N'User5', N'User90', 3)
GO
SET IDENTITY_INSERT [dbo].[RequestDetail] OFF
GO

Try

select RD.RequestId, Rd.RequestName, RD.ProjectId, RD.RequestedBy,
stuff((select ', ' + RequestModifiedBy from dbo.RequestDetail R where R.RequestID = RD.RequestID order by R.RequestModifiedBy
FOR XML PATH('')),1,2,'') as ModifiedBy

from dbo.RequestDetail RD
GROUP BY RD.RequestId, Rd.RequestName, RD.ProjectId, RD.RequestedBy

If XML gives you the heebeegeebies, give this a go:

DECLARE @RequestDetail TABLE(ID INT NOT NULL,RequestID INT NOT NULL,RequestName VARCHAR(50) NOT NULL,RequestedBy VARCHAR(50) NOT NULL,RequestModifiedBy VARCHAR(50) NOT NULL,ProjectId INT NOT NULL)

INSERT @RequestDetail (ID, RequestID, RequestName, RequestedBy, RequestModifiedBy, ProjectId) VALUES (1, 501, N'OS Installation', N'User1', N'User2', 1)
INSERT @RequestDetail (ID, RequestID, RequestName, RequestedBy, RequestModifiedBy, ProjectId) VALUES (2, 501, N'OS Installation', N'User1', N'User3', 1)
INSERT @RequestDetail (ID, RequestID, RequestName, RequestedBy, RequestModifiedBy, ProjectId) VALUES (3, 501, N'OS Installation', N'User1', N'User4', 1)
INSERT @RequestDetail (ID, RequestID, RequestName, RequestedBy, RequestModifiedBy, ProjectId) VALUES (4, 502, N'Driver Installation', N'User5', N'User74', 3)
INSERT @RequestDetail (ID, RequestID, RequestName, RequestedBy, RequestModifiedBy, ProjectId) VALUES (5, 502, N'Driver Installation', N'User5', N'User81', 3)
INSERT @RequestDetail (ID, RequestID, RequestName, RequestedBy, RequestModifiedBy, ProjectId) VALUES (6, 502, N'Driver Installation', N'User5', N'User90', 3)

; WITH base AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY ID) AS seq
  FROM @RequestDetail
), rCTE AS (
SELECT RequestID, RequestName, RequestedBy, CAST(RequestModifiedBy AS NVARCHAR(MAX)) AS RequestModifiedBy, ProjectID, seq
  FROM base
 WHERE seq = 1
UNION ALL
SELECT r.RequestID, r.RequestName, r.RequestedBy, r.RequestModifiedBy +', '+a.RequestModifiedBy, r.ProjectID, a.seq
  FROM rCTE r
    INNER JOIN base a
	  ON r.RequestID = a.RequestID
	  AND r.seq + 1 = a.seq
)

SELECT *
  FROM rCTE r
 WHERE seq = (SELECT MAX(seq) FROM rCTE WHERE RequestID = r.RequestID)