SQL Query Consolidate Rows Parts

Looking to consolidate the output rows from @MachineOut where company codes are all the same (cant select a max) and check another table for last detected company code

Last Detected Table Data

MACHINE1	USER2	03327-9715131
MACHINE2	USER1	03361-9724327

Select all Open in new window

MachineGUID	Region	Machine	NBK	DevicePurpose	CompanyCode	CompanyCodeCnt	MailCode	Model	Status
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03017-9714083	2	NY1-100-05-00	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03017-9714159	1	IL4-540-28-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715129	1	473-808-02-71	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715131	2	473-808-02-71	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000611	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000636	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-9954551	1	737-801-18-01	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03017-9724709	3	839-000-00-00	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03361-9724327	3	839-000-00-00	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	Not Available	1	Not Available	Tier 1	IN USE

Select all Open in new window

Current Output from Query

Select mo1.MachineGUID, mo1.Region, mo1.Machine, mo1.NBK, mo1.DevicePurpose, mo1.CompanyCode, mo1.CompanyCodeCnt, mo1.MailCode, mo1.Model, mo1.[Status] from @MachineOut mo1
      WHERE CompanyCodeCnt > 1 AND CompanyCodeCnt IN (SELECT MAX(CompanyCodeCnt) FROM @MachineOut mo Where mo.Machine = mo1.Machine)
      GROUP BY MachineGUID, Region, Machine, NBK, DevicePurpose, CompanyCode, CompanyCodeCnt, MailCode, Model, [Status]
      HAVING SUM(LEN(mo1.NBK) - LEN(REPLACE(mo1.NBK,',','')) + 1) > 2

MachineGUID	Region	Machine	NBK	DevicePurpose	CompanyCode	CompanyCodeCnt	MailCode	Model	Status
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03017-9714083	2	NY1-100-05-00	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715131	2	473-808-02-71	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000611	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000636	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03017-9724709	3	839-000-00-00	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03361-9724327	3	839-000-00-00	Tier 1	IN USE

Select all Open in new window

Need Output like this

Region	Machine	NBK	DevicePurpose	CompanyCode	CompanyCodeCnt	MailCode	Model	Status
APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715131	2	473-808-02-71	Tier 1	IN USE
APAC	MACHINE2	USER1,USER2,USER3	Not Available	03361-9724327	3	839-000-00-00	Tier 1	IN USE

Select all Open in new window

Thanks in advance

M