There's many ways to achieve that...
1) using
CTE[
^].
Before i show you how to use it, i need to mention that your database structure is wrong. You need a "in-between-table" which will link subjects with students. See:
DECLARE @tempStudent TABLE( Student_Id int, [Name] nvarchar(10), Class nvarchar(10))
Insert Into @tempStudent Values(1,'Joy', 'C1'),(2,'Root', 'C2'),(3,'Devil', 'C3')
DECLARE @tempSubject TABLE( Subject_Id int, SubjectName nvarchar(10))
Insert Into @tempSubject Values(1,'English'),(2,'Math'),(3,'Science')
DECLARE @tempSubjectStudent TABLE( Student_Id int, Subject_Id int)
Insert Into @tempSubjectStudent Values(1, 1),(1,2),(2,3),(2,2),(3,3),(3,1)
Now, an example CTE query:
;WITH CTE AS
(
SELECT DISTINCT st.Student_Id, st.[Name], st.Class, CONVERT(NVARCHAR(MAX), '') AS SubjectName,
0 AS RowNo, 0 AS Subject_Id , COUNT(*) OVER(PARTITION BY st.Student_Id) AS NoOfSubjects
FROM @tempStudent st INNER JOIN @tempSubjectStudent ss ON st.Student_Id = ss.Student_Id
UNION ALL
SELECT st.Student_Id, st.[Name], st.Class, CONCAT(st.SubjectName, su.SubjectName, ', ') AS SubjectName,
st.RowNo + 1 AS RowNo, ss.Subject_Id AS Subject_Id ,st.NoOfSubjects
FROM CTE st
INNER JOIN @tempSubjectStudent ss ON st.Student_Id = ss.Student_Id
INNER JOIN @tempSubject su ON ss.Subject_Id = su.Subject_Id
WHERE st.RowNo<= st.NoOfSubjects AND ss.Subject_Id > st.Subject_Id
)
SELECT [Name], Class, SubjectName
FROM CTE
WHERE RowNo=NoOfSubjects
Result:
Name Class SubjectName
Devil C3 English, Science,
Root C2 Math, Science,
Joy C1 English, Math,
For further details, please see:
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[
^]
Common Table Expressions (Introduction to CTE's) - Essential SQL[
^]
Using a Recursive CTE to Generate a List – SQLServerCentral[
^]
2) using
FOR XML [
^]
See:
How to concatenate text from multiple rows into a single text string in SQL server? - Stack Overflow[
^]
Examples: Using PATH Mode - SQL Server | Microsoft Docs[
^]
How Stuff and 'For Xml Path' work in Sql Server - Stack Overflow[
^]