Select date range into different column – Laravel

You aren’t providing anything like your attempted query, how you are passing the date ( it is a range, month only etc ), and your desired json ouput.

its hard to even assume how you are going to do things specially you are passing a column value as column name in your desired result (which doesn’t make much sense with raw sql query unless those columns
aren’t dynamic).

but to give you a starting point, you can simply group them by name, then date, then do another grouping by date in the collection

e.i;

$result = DB::table('table_name')->select([
    'name',
    'date',
])
->selectRaw('sum(score) AS score')
->groupBy(['name', 'date'])->get();

return $result->groupBy('date');

then you should be able to get result in a format like below;

{
    "01-01-2023" : [
        {
            "name": "A",
            "date": "01-01-2023",
            "score": "300"
        }
    ],
    "02-01-2023" : [
        {
            "name": "A",
            "date": "02-01-2023",
            "score": "300"
        }
        {
            "name": "B",
            "date": "02-01-2023",
            "score": "200"
        }
    ],
    "03-01-2023" : [
        .
        .
        .
    ]
}

For you desired table result, thats better be changed to a dynamic rows instead of dynamic column

EDIT

In reference with Karl answer, you can loop through a date range and inject additional select statement.

e.i. current month dates

$dateRange = CarbonCarbonPeriod::create(now()->startOfMonth(), now()->endOfMonth() )->toArray();

$result = DB::table('table_name')->select(['name']);

foreach ($dateRange as $date) {
    $dateFormat = $date->format('d-m-Y');
    $day = $date->format('j');
    $result->selectRaw("SUM(CASE WHEN Date = '$dateFormat' THEN Score ELSE 0 END) AS 'Day $day'");
}

return $result->groupBy('name')->get();