I have 2 table I am join from, namely Requests and RequestComments
1 Request can have multiple comment
<code>SELECT
r.[Reference],
c.DateCreated,
c.[Sender],
c.[Comment],
r.[Client Surname],
r.[Client Address]
FROM Requests r
CROSS APPLY (select TOP 5 rc.DateCreated,
rc.[Sender],
rc.[Comment] from RequestComments rc where rc.[Reference] = r.[RequestRef] order by rc.DateCreated) c
</code>
<code>SELECT
r.[Reference],
c.DateCreated,
c.[Sender],
c.[Comment],
r.[Client Surname],
r.[Client Address]
FROM Requests r
CROSS APPLY (select TOP 5 rc.DateCreated,
rc.[Sender],
rc.[Comment] from RequestComments rc where rc.[Reference] = r.[RequestRef] order by rc.DateCreated) c
</code>
SELECT
r.[Reference],
c.DateCreated,
c.[Sender],
c.[Comment],
r.[Client Surname],
r.[Client Address]
FROM Requests r
CROSS APPLY (select TOP 5 rc.DateCreated,
rc.[Sender],
rc.[Comment] from RequestComments rc where rc.[Reference] = r.[RequestRef] order by rc.DateCreated) c
I need to need to get the DateCreated, Sender and Comment in 5 different columns within the same row wothout duplicates.
Currently rows are duplicating.
I have also tried a pivot with no lick.
My output should look something like this
1