sql server - SQL Query how to summarize students record by date? -

i have following table students

id  studentid       subject         date          grade  1   001     math        02/20/2013   2   001     literature  03/02/2013  b  3   002     biology     01/01/2013   4   003     biology     04/08/2013   5   001     biology     05/01/2013  b  6   002     math        03/10/2013  c 

i need result another table called studentreport shown below. table cumulative report of students records in chronological order date.

id studentid       report  1   001     #biology;b;05/01/2013#literature;b;03/02/2013#math;a;02/20/2013  2   002     #math;c;03/10/2013#biology;a;01/01/2013  3   003     #biology;a;04/08/2013 

typically not store data in table, have data needed generate report.

sql server not have easy way generate comma-separated list have use for xml path create list:

;with cte (   select id,      studentid,     date,     '#'+subject+';'+grade+';'+convert(varchar(10), date, 101) report   student )  -- insert studentreport select distinct    studentid,   stuff(          (select cast(t2.report varchar(50))           cte t2           c.studentid = t2.studentid           order t2.date desc           xml path (''))           , 1, 0, '')  report cte c; 

see sql fiddle demo (includes insert new table). give result:

| id | studentid |                                                          report | ------------------------------------------------------------------------------------ | 10 |         1 | #biology;b;05/01/2013#literature;b;03/02/2013#math;a;02/20/2013 | | 11 |         2 |                         #math;c;03/10/2013#biology;a;01/01/2013 | | 12 |         3 |                                           #biology;a;04/08/2013 |