Getting records from row wise to column wise in json format laravel

It's always a bit tricky with nested relations.

You need to do something like

SELECT  e.name, 
(SELECT er.marks FROM exam_results er WHERE er.exam_id = e.id AND subject = 'english') marks_english,
(SELECT er.marks FROM exam_results er WHERE er.exam_id = e.id AND subject = 'math') marks_math,
FROM exams e;

but subject can vary from student to student

You should set a relation on your models and use Eloquent.

Example would be:

class Exam extends Model
{
    public function results()
    {
        return $this->hasMany(ExamResult::class);
    }
}

Then you could do something like this:

    return App\Models\Exam::with('results')->get()->map(function(App\Models\Exam $exam){
        return [
            'Exam_Name' => $exam->name,
            'marks' => $exam->results->pluck('marks', 'subject')
        ];
    })->toJson();

There are students also involved. i am trying to get it student id wise.

but this way it throws all the exam ,results and random marks

So you have the student

$student = Student::findOrFail($id);

With that student you can get all it's marks

$studentMarks = Marks::where('student_id', $student->id)->get();

Now since you want it for a certain term you add another where clause.

$studentMarks = Marks::where('student_id', $student->id)->where('term_id', $term->id)->get();

If you have set up the correct relationhips you cand do something like this

$student = Student::findOrFail($id)->with('marks');

if u see my result it is student wise this is the result of the one student.

The problem is it is expanded subject wise but i want to combine all subjects under exam name.

I have made some changes to controller file and got the result

data:
0:
Exam_Name : "first term"
marks : ["English","Math","Science"]  : ["75","80","70"]
1:
Exam_Name : "second term"
marks : ["English","Math","Science"]  : ["72","82","86"]

But i want it like

data:
0:
Exam_Name : "first term"
marks : "English : 75, Math : 80, Science : 70"
1:
Exam_Name : "second term"
marks : "English : 72, Math : 82, Science : 86"

Here is controller file

public function exam($id)
	{
		$student = Student::find($id);		
		$exams = ExamResults::where('student_id',$id)->select('exam_id')
                 ->orderBy('exam_id','DESC')
                ->distinct() - >pluck('exam_id')->toArray();
		
$data   = [];			
		foreach($exams as $exam)
		{
                 $exm = Exam::where('id',$exam)->first();

		 $subject = ExamResults::where('exam_id',$exm->id)
                  ->where('student_id',$student->id)->pluck('subject');

		 $mark = ExamResults::where('exam_id',$exm->id)
                 ->where('student_id',$student->id) ->pluck('marks');
		 
		$data[] = [			
			'exam_name' 	=> $exm->name,
			'marks'         => $subject." : ".$mark
				
			];
		
		}
		
return response()->json(['data' => $data]);
	}

You can do it with a single query, groupBy and map:

    return DB::table('exam_results')
        ->join('exams', 'exams.id', 'exam_results.exam_id')
        ->where('exam_results.student_id', $student_id)
        ->get()
        ->groupBy('exam_id')
        ->map(function(\Illuminate\Support\Collection $exams) {
            return [
                'Exam_Name' => $exams->first()->name,
                'marks' => $exams->pluck('marks','subject')
            ];
        })->values();

This will return:

[
  {
    "Exam_Name": "first term",
    "marks": {
      "english": 75,
      "math": 80,
      "science": 70
    }
  },
  {
    "Exam_Name": "second term",
    "marks": {
      "english": 72,
      "math": 82,
      "science": 86
    }
  }
]

by this i am getting. subject names are not showing and getting only the last marks in the table

	
0	
Exam_Name	"first term"
marks	
	             :   	"70"
1	
Exam_Name	"second term"
marks	
                     :   	"86"

the solution that I've sent you should work.

Is your column name marks or mark?

its marks