We help IT Professionals succeed at work.
Get Started

SQL Query Consolidate Rows Parts

57 Views
Last Modified: 2016-03-07
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

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

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

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

Open in new window

Thanks in advance

M
Comment
Watch Question
Senior Windows Automation Developer
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE