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

Mark Tabor

6y1.2k.NET

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 ?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nikunj Satasiya6y ago
Accepted 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.
+2
Mark Tabor6y ago
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.
0
Mark Tabor6y ago
ok
Nikunj Satasiya.
0
Nikunj Satasiya6y ago
Ok mark let's i will made a query for you as per your requirement you described in the question above.
0
Mark Tabor6y ago
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;
0
Nikunj Satasiya6y ago
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.
0

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

Nikunj Satasiya.