Sql data vertical to horizontal and Laravel

hi

  $year = $request->year;
                    $month = $request->month;

                    // dd($year);

                        $recordsNew = DB::table('dbo.sales')
                        ->leftjoin('dbo.stores', 'dbo.sales.storename', '=', 'dbo.stores.storename')
                        ->when(!empty($request->store), function ($query) use ($request) {
                            return $query->where('dbo.sales.storename', 'like', '%' . $request->store . '%');
                        })
                        ->where(DB::raw('YEAR(BillDate)'), $year)
                        ->where(DB::raw('MONTH(BillDate)'), $month)
                        ->select(DB::raw("SUM(TotalPricewithTax) as totalpricewithtax"), 'dbo.sales.storename', DB::raw('CAST(BillDate as DATE) as datenew'))
                        ->groupBy(DB::raw('CAST(BillDate as DATE)'),'dbo.sales.storename')
                        ->orderBy(DB::raw("CAST(BillDate as DATE)"), 'asc')
                        ->get();

iam getting data below like this

datenew        storename       totalpricewithtax
----------     ---------       -------------------
2021-01-01     A                5260
2021-01-01     B                6520
2021-01-01     C                7320  
2021-01-01     D                6590
2021-01-02     A                5963
2021-01-02     B                2000
2021-01-02     C                1963
2021-01-02     D                2963
-
-
-
go on

but i want to display vertical to horizontal below like this

datenew           A           B          C            D      ----- ---- ---- -- go on (no of stores)
----------     ---------  ---------  ---------   ----------  
2021-01-01       5260        6520      7320        6590 
2021-01-02       5963        2000      1963        2963 

how to achive this, can anybody help in this scenario,

Answer

Something like this will do it:

$recordsKeyedByDate = [];
foreach ($recordsNew as $record) {
    $recordsKeyedByDate[$record->datenew][$record->storename] = $record->totalpricewithtax;
}

$storeNames = ['A', 'B', ...];

foreach ($recordsKeyedByDate as $date => $recordsKeyedByStoreName) {
    // output $date
    foreach ($storeNames as $storeName) {
        // output $recordsKeyedByStoreName[$storeName]->totalpricewithtax
    }
}