PHP MySQL Grouping Issue

If I have a table with the following data in MySQL:

Company           CompanyEntity        OrderNumber      Delivery Date
Deeway HS              20779             4608580         2020-11-11
Deeway Pearl HS        20780             4608580         2020-11-11
Deeway Stony HS        1273              4608558         2020-11-11
Zaper LTD              9995              4630230         2020-11-11
Zaper CP LTD           4295              4607371         2020-11-11
Wilder                 4224              6630210         2020-11-11
Wilder                 4224              7601371         2020-11-11

I need it to look like this:

Company                                        CompanyEntity        OrderNumber      Delivery Date
Deeway HS,Deeway Pearl HS,Deeway Stony HS     20779,20780,1273     4608580,4608558    2020-11-11
Zaper LTD,Zaper CP LTD                           9995,4295         4630230,4607371    2020-11-11
Wilder                                             4224            6630210,7601371    2020-11-11

I’ve tried:

SELECT
nextgenorder_companyname,
nextgenorder_company_entity,
nextgenorder_ordernumber,
nextgenorder_deliverydate
    FROM nextgenorders2
WHERE nextgenorder_deliverydate='2020-11-11'
GROUP BY nextgenorder_companyname,nextgenorder_company_entity
ORDER BY nextgenorder_companyname

Which does not give me the results I want. Any help would be greatly appreciated.

SQL Fiddle

Thank you

Answer

You need to use GROUP_CONCAT to aggregate your values, and based on your sample data you want to group by the first word in the name, so you can use SUBSTRING_INDEX to extract that. To deal with possible / in the name as a separator instead of space, you can REPLACE them:

SELECT
    GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS Company,
    GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS CompanyEntity,
    GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS OrderNumber,
    GROUP_CONCAT(DISTINCT nextgenorder_deliverydate) AS `Delivery Date`
FROM nextgenorders2
WHERE nextgenorder_deliverydate='2020-11-11'
GROUP BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)
ORDER BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)

Output:

Company                                     CompanyEntity       OrderNumber         Delivery Date
Deeway HS,Deeway Pearl HS,Deeway Stony HS   20779,20780,1273    4608580,4608558     2020-11-11
Wilder                                      4224                6630210,7601371     2020-11-11
Zaper/LTD,Zaper CP LTD                      9995,4295           4630230,4607371     2020-11-11

Demo on SQLFiddle