0

I have a (temp) table which contains student records.
There is another table which contains which students enrolled for which subjects.
I would like to update the student table with subjects aggregated into it as shown below. 

StudentID   Name    Subjects
1           Ryan    
2           James   

StudentID   Subject 
1           Maths   
1           Science 
2           History 

StudentID   Name    Subjects
1           Ryan    Maths, Science
2           James   History

I've tried this, but it updates only with first record found. Aggregation wont happen. 

declare @Subjects NVARCHAR(max) = ''

UPDATE #Students
SET Subjects = (@Subjects + CASE @Subjects WHEN '' THEN '' ELSE '; ' END + ISNULL(StudentSubjects.Subject,' '))
FROM #Students 
    INNER JOIN StudentSubjects ON #Students.StudentID = StudentSubjects.StudentID

SQL Server 2008 R2.
EDIT:
I am updating the temp table inside a stored procedure which will be rendered out in a resultset. Not updating the actual data.

Null Head
  • 2,877
  • 13
  • 61
  • 83
  • 5
    That is such a bad idea, I don't want to show you how to do it. You shouldn't store comma delimited lists in columns. – Gordon Linoff Sep 23 '14 at 22:16
  • 3
    You should not store these results permanently in the database. You can create a view using techniques from this question - http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – Bulat Sep 23 '14 at 22:21
  • possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Kyle Hale Sep 23 '14 at 22:26
  • Updated my question to be more clear, Gordon and Bulat. Apparently putting temp in brackets did not leave any impression that nature of data to be updated is temporary! – Null Head Sep 23 '14 at 23:00
  • The main point is that this is a duplicate question. – Bulat Sep 23 '14 at 23:31

1 Answers1

1

I hope the below query will work for you

UPDATE #Students
SET Subjects = STUFF((SELECT ','+Subject From StudentSubjects b WHERE b.StudentID=a.StudentID 
For XML PATH ('')),1,1,'') 
from #Students a
ram_sql
  • 404
  • 2
  • 9