CODEWITHSUNDEEP
sqlsql-server
Think
Think

Reputation: 261

SQL Query how to summarize students record by date?

I have a following table Students

Id  StudentId       Subject         Date          Grade

1   001     Math        02/20/2013  A

2   001     Literature  03/02/2013  B

3   002     Biology     01/01/2013  A

4   003     Biology     04/08/2013  A

5   001     Biology     05/01/2013  B

6   002     Math        03/10/2013  C

I need result into another table called StudentReport as shown below. This table is the cumulative report of all students records in chronological order by 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

Upvotes: 0

Views: 680

Answers (3)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

Without using CTE.Improve performance.

FOR XML

(select 
min(ID) as ID,
StudentId,
STUFF((select ', '+'#'+s2.Subject+';'+s2.Grade+';'+convert(varchar(25),s2.Date)
from student s2
where s2.StudentId=s1.StudentId
FOR XML PATH (''))
,1,2,'') as report
into t
 from student s1
group by StudentId)
;

select * from t

Where t would be new Table Name which doesn't exists in DataBase.

SQL Fiddle

Upvotes: 0

Taryn
Taryn

Reputation: 247840

Typically you would not store this data in a table, you have all the data needed to generate the report.

SQL Server does not have an easy way to generate a comma-separated list so you will have to use FOR XML PATH to create the list:

;with cte as
(
  select id, 
    studentid,
    date,
    '#'+subject+';'+grade+';'+convert(varchar(10), date, 101) report
  from student
) 
-- insert into studentreport
select distinct 
  studentid,
  STUFF(
         (SELECT cast(t2.report as varchar(50))
          FROM cte t2
          where c.StudentId = t2.StudentId
          order by t2.date desc
          FOR XML PATH (''))
          , 1, 0, '')  AS report
from cte c;

See SQL Fiddle with Demo (includes an insert into the new table). Give a 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 |

Upvotes: 3

A. Still
A. Still

Reputation: 300

If you are looking to insert the data from 'Students' into 'Student Report', try:

INSERT INTO StudentReport (ID, StudentID, Report)
SELECT ID, StudentID, '#' + subject + ';' + grade + ';' + date AS report
FROM Students

Upvotes: 0

Related Questions