Click here to Skip to main content
15,890,123 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
CREATE TABLE #tempStudent
(
	Student_Id int,
	Name nvarchar(10),
	Class nvarchar(10)
)
Insert Into #tempStudent Values(1,'Joy', 'C1'),
(2,'Root', 'C2'),
(3,'Devil', 'C3')
CREATE TABLE #tempSubject
(
	Student_Id int,
	SubjectName nvarchar(10)
)
Insert Into #tempSubject Values
(1,'English'),
(1,'Math'),
(2,'Science'),
(2,'Math'),
(3,'Science'),
(3,'English')


Want to Result as:
Name	Class	SubjectName
Joy		C1		English,Math
Root	C2		Science,Math
Devil	C3		Science,English


What I have tried:

SQL
select st.Name, st.Class, sb.SubjectName from #tempSubject as sb
inner join #tempStudent as st on sb.Student_Id=st.Student_Id


Current Result
Name	Class	SubjectName
Joy		C1		English
Joy		C1		Math
Root	C2		Science
Root	C2		Math
Devil	C3		Science
Devil	C3		English
Posted
Updated 19-Sep-19 0:33am
v2

1 solution

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:
SQL
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')
--new table with relationship many (Student_id) to many (Subject_id)
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:
SQL
;WITH CTE AS
(
	--initial query
	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 
	--recursive part
	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[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900