how to show one row from multiple rows with one column havin

Question

Hi I have a Use case in which I have three tables as Teacher table , Courses Table, and TeacherVsCourse Table Course_ Id Name 1 MathMatics 2 Physics The Teacher Table Teacher_ID Teacher_Name 1 AAA 2 BBB The TeacherVsCourses Table id Course_id Teacher_id 1 1 1 2 2 1 3 1 2 My Application is having MVC in asp.net architecture Now I am saving individual records into the database table as shown below you can check the image from here i have tried to add image but it gives error so i upload it here https://imgur.com/a/WQLeYjC ------ what i want onto my view instead of two rows for teacher-id =1 , I need 1 row with comma separated courses how to do it ?

Nikunj Satasiya · Answer

CourseTable DECLARE @CourseTable AS TABLE ( Course_Id INT , Name NVARCHAR(50) ) TeacherTable DECLARE @TeacherTable AS TABLE ( Teacher_Id INT , Name NVARCHAR(50) ) TeacherVSCourseTable DECLARE @TeacherVSCourseTable AS TABLE ( Id INT , Course_Id INT , Teacher_Id INT ) Insert Sample Record CourseTable INSERT INTO @CourseTable (Course_Id, Name ) VALUES (1, N 'MathMatics' ), (2, N 'Physics' ) Insert Sample Record TeacherTable INSERT INTO @TeacherTable (Teacher_Id, Name ) VALUES (1, N 'AAA' ), (2, N 'BBB' ) Insert Sample Record TeacherVSCourseTable INSERT INTO @TeacherVSCourseTable (Id, Course_Id, Teacher_Id) VALUES (1, 1, 1), (2, 2 ,1), (3, 1 ,2) Join the above tables using CTC ; WITH TeacherVSCourse (Id,CourseName,TeacherName ) AS ( SELECT TeacherVSCourse.Id, Course. Name AS CourseName,Teacher. Name AS TeacherName FROM @TeacherVSCourseTable AS TeacherVSCourse LEFT JOIN @CourseTable AS Course ON TeacherVSCourse.Course_Id = Course.Course_Id LEFT JOIN @TeacherTable AS Teacher ON TeacherVSCourse.Teacher_Id = Teacher.Teacher_Id ) Display teacher wise comma-separated courses using FTUFF() function SELECT t2.TeacherName, STUFF(( SELECT ', ' + CourseName FROM TeacherVSCourse t1 WHERE t1.TeacherName = t2.TeacherName FOR XML PATH ( '' )), 1, 2, '' ) AS CourseName FROM TeacherVSCourse t2 GROUP BY t2.TeacherName For more information about STUFF() Function Please visit : https://www.codingvila.com/2019/02/sql-server-stuff-function-with-syntax-and-example.html I Hope This Helps You.

Mark Tabor · Answer

Hi If i need to add the semester column name as well , as i have a semester base table having columns(id,name) and in teacherandcourses i have semesterid as well so how can i take the semester columns as well as the list of courses , can i have comma seperated semester as well.

Mark Tabor · Answer

ok Nikunj Satasiya.

Nikunj Satasiya · Answer

Ok mark let's i will made a query for you as per your requirement you described in the question above.

Mark Tabor · Answer

Thanks for sharing very useful link , But in my case i have these tables to get the course name i need to join with courses table and to get the teacher name i need to join with teacher table how to do that . SELECT DepartmentId, DepartmentName, EmployeeName = STUFF(( SELECT ', ' + EmployeeName FROM @TableT t1 WHERE t1.DepartmentId = t2.DepartmentId FOR XML PATH ( '' )) , 1, 2, '' ) from @TableT t2 GROUP BY DepartmentId, DepartmentName;

Nikunj Satasiya · Answer

Hi mark, if you're using SQL Server then you can use stuff() function to display teacher wise comma separated courses, and can bind query results to your MVC application, please follow this link to understand working of stuff() function with example. Link: https://www.codingvila.com/2019/02/sql-server-stuff-function-with-syntax-and-example.html I hope this helps you.

how to show one row from multiple rows with one column havin

Answers (6)