0

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

enter image description here

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

enter image description here

How can i write a sql to achieve this.

Thanks in advance

SqlLearner
  • 763
  • 8
  • 23
  • 37
  • Actually [this is probably a better duplicate](http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query). – Aaron Bertrand Oct 21 '14 at 21:01

1 Answers1

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • and 'AS Note' after STUFF(...) to label the column :) – user3885927 Oct 21 '14 at 20:58
  • Be careful about just using XML PATH without TYPE; in this case it's probably fine but data can get messed up in other scenarios. Please see [this blog post](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) for more information (also there is better syntax in [this answer](https://stackoverflow.com/a/1818045/61305)). Finally, [DO NOT convert to `varchar` without length](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). – Aaron Bertrand Oct 21 '14 at 21:04