56
Views
8
Comments
Combine data to a single cell in excel
Question

Hi,

I have 2 entities named "Student" which hold the basic information of the student and another entity called "Lesson". The "Lesson" Entity has one to many relation to the "Student" entity as a student can sign up for multiple lessons.

My question is when exporting the data to excel, I want to combine all the lessons of the student into one cell separated by a delimiter. As of now, each student may have multiple rows each corresponding to different lesson dates in the excel file. I want to limit it to one row per student.


Thank you

Rank: #89

Hi Chew,

You nee to use advance sql for that, create a structure accrodingly and use SQL like below

https://www.c-sharpcorner.com/UploadFile/860b29/concatenate-multiple-rows-within-single-row-in-sql-server-20/  

SELECT t1.StudentID,  
CourseNames=STUFF  
(  
    (  
      SELECT DISTINCT ', '+ CAST(g.CourseName AS VARCHAR(MAX))  
      FROM {Courses} g,{StudentCourses} e   
      WHERE g.CourseID=e.CourseID and e.StudentID=t1.StudentID   
      FOR XMl PATH('')  
    ),1,1,''  
)  
FROM {StudentCourses} t1  
GROUP BY t1.StudentID

And use your structure for download to excel.


Hope this will help you.

Regards

Rahul Sahu

Rank: #89

Welcome Chew,

Glad to help you.


Cheers

Rank: #89

Hi Chew,

Can you post entities strucute or oml or query.


Regards

Rahul sahu