How to concatenate the text when group by a partic

Question

This question already has an answer here:

• Simulating group_concat MySQL function in Microsoft SQL Server 2005? 10 answers

Hi I have a table StudentNote with three fields 'StudentID','Notes','Date'.i have the following values

Now my requirement is ,i wanted to group the above table on StudentID and Concatenate Note and Date Fields like

How can i write a sql to achieve this.

Thanks in advance

Answer 1

try this

SELECT T1.STUDENTID,
       STUFF((SELECT ',' + CONVERT(VARCHAR(50), NOTE) + ','
                     + CONVERT(VARCHAR(50), DATE)
              FROM   STUDENTNOTE B
              WHERE  B.STUDENTID = T1.STUDENTID
              FOR XML PATH('')), 1, 1, '') [NOTE]
FROM   STUDENTNOTE T1
GROUP  BY T1.STUDENTID