Combine data to a single cell in excel

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

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

Rahul Sahu wrote:

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

 Hi Rahul,


Thank you for the helpful reply! Solved my issue.


Welcome Chew,

Glad to help you.


Cheers

Rahul Sahu wrote:

Welcome Chew,

Glad to help you.


Cheers

 

 Hi Rahul,

Just another question regarding the issue. Because my lessons have a DateTime component. The SQL Query cast it to VARCHAR (Text data type) as I have to combine all the dates. I have issues now assigning that value to the list from the "Lessons" aggregate as it expects a DateTime data type. (The lesson date-time is saved as a date time data type).


How do I resolve this issue? I need to assign to that list as a I am exporting that list to excel.

Best Regards,

Harris Chew

Hi Chew,

Can you post entities strucute or oml or query.


Regards

Rahul sahu

 wrote:

Hi Chew,

Can you post entities strucute or oml or query.


Regards

Rahul sahu

 

 Hi Rahul,


The 1st photo Attached is the Entity Attribute TimeStamp of the Lesson. You can see that it's data type is DateTime


However, in the SQL Query, the TimeStamp is returned as VarChar. Thus, if I assign this output to the Timestamp of Date Time data type, it gives an error. How do I work around it?


This is the Logic.

Thank you for your advice!


Best Regards,

Harris Chew

Chew Harris wrote:

 wrote:

Hi Chew,

Can you post entities strucute or oml or query.


Regards

Rahul sahu

 

 Hi Rahul,



The 1st photo Attached is the Entity Attribute TimeStamp of the Lesson. You can see that it's data type is DateTime


However, in the SQL Query, the TimeStamp is returned as VarChar. Thus, if I assign this output to the Timestamp of Date Time data type, it gives an error. How do I work around it?


This is the Logic.

Thank you for your advice!


Best Regards,

Harris Chew

 

 HI Chew,

In this case you need to catch all combine dates in Text type column and download it, because when you combine all date it convert the data type in text type that why you get error.

Use text type column to hold this value.


Regards

Rahul Sahu

Rahul Sahu wrote:

Chew Harris wrote:

 wrote:

Hi Chew,

Can you post entities strucute or oml or query.


Regards

Rahul sahu

 

 Hi Rahul,



The 1st photo Attached is the Entity Attribute TimeStamp of the Lesson. You can see that it's data type is DateTime


However, in the SQL Query, the TimeStamp is returned as VarChar. Thus, if I assign this output to the Timestamp of Date Time data type, it gives an error. How do I work around it?


This is the Logic.

Thank you for your advice!


Best Regards,

Harris Chew

 

 HI Chew,

In this case you need to catch all combine dates in Text type column and download it, because when you combine all date it convert the data type in text type that why you get error.

Use text type column to hold this value.


Regards

Rahul Sahu

 

 Hi Rahul,

Thank you for your reply. I understand.

Best Regards,

Harris Chew