MySQL query to combine rows

Question

I have the following MySQL query.

SELECT h.hostname
     , c.name
     , d.cim_item
     , t.datastatus
     , DATEDIFF(NOW(), s.time) 
  FROM plugin_sw_vmware_healthmon_hosts h
  JOIN 
     ( SELECT clientid
            , locationid
            , hostname
            , MAX(scantime) Time
         FROM labtech.plugin_sw_vmware_healthmon_scans
        GROUP  
           BY clientid
            , locationid
            , hostname
     ) s
    ON h.clientid = s.clientid
   AND h.locationid = s.locationid
   AND h.hostname = s.hostname
  JOIN clients c
    ON c.clientid = h.clientid 
  JOIN plugin_sw_vmware_healthmon_cimdata d
    ON d.clientid = h.clientid
   AND d.locationid = h.locationid 
   AND d.hostname = h.hostname 
  JOIN plugin_sw_vmware_healthmon_types t
    ON t.datavalue = d.cim_value 
   AND t.datatype = h.vender 
 WHERE d.cim_item LIKE '%critical array%' 
 ORDER  
    BY c.name;

The output is here:

ESXi Host Name Client Name Item Type      RAID Status Last Updated (days ago)
192.14.13.2    Corp1        disk array 1  Good                              0
192.14.13.2    Corp1        disk array 2  Good                              0
192.14.13.2    Corp1        disk array 3  Good                              0
192.14.13.2    Corp1        disk array 4  Good                              0
192.14.13.2    Corp1        drisk array 5 Good                              0
192.16.11.5    Corp 2       disk array 1  Good                              4
192.16.11.5    Corp 2       disk array 2  Good                              4
192.16.11.5    Corp 2       disk array 3  Good                              4
192.16.11.5    Corp 2       disk array 4  Good                              4
192.16.11.5    Corp 2       drisk array 5 Good                              4

What I would like to do is combine all the results for Corp1 in 1 row and Corp2 in a second row so that it looks like this:

ESXi Host Name Client Name Item Type                                                            RAID Status Last Updated (days ago)
192.14.13.2    Corp1       disk array 1, disk array 2, disk array 3, disk array 4, disk array 5 Good                              0
192.16.11.5    Corp2       disk array 1, disk array 2, disk array 3, disk array 4, disk array 5 Good                              0

Any ideas how to do this in my query?

Add your table schema.

Abhishekh Gupta
– Abhishekh Gupta

2015-06-05 21:44:01 +00:00
Commented Jun 5, 2015 at 21:44 — Abhishekh Gupta
– Abhishekh Gupta, Commented Jun 5, 2015 at 21:44

user2941651 · Accepted Answer · 2015-06-05 21:47:14Z

You could try GROUP_CONCAT, like the following:

SELECT hosts.HostName,
       clients.Name,
       GROUP_CONCAT(cimdata.CIM_Item SEPARATOR ", ") AS CIM_Items,
       types.DataStatus,
       datediff(NOW(), scans.Time)
FROM plugin_sw_vmware_healthmon_hosts AS hosts
JOIN (SELECT ClientID,
             LocationID,
             HostName,
             max(ScanTime) AS 'Time'
      FROM labtech.plugin_sw_vmware_healthmon_scans
      GROUP BY ClientID,
            LocationID,
            HostName) AS scans ON hosts.ClientID=scans.ClientID
  AND hosts.LocationID=scans.LocationID
  AND hosts.HostName=scans.HostName
JOIN clients ON hosts.ClientID=clients.ClientID
JOIN plugin_sw_vmware_healthmon_cimdata AS cimdata ON hosts.ClientID=cimdata.ClientID
  AND hosts.LocationID=cimdata.LocationID
  AND hosts.HostName=cimdata.HostName
JOIN plugin_sw_vmware_healthmon_types AS types ON cimdata.CIM_Value=types.DataValue
  AND hosts.Vender=types.DataType
WHERE cimdata.CIM_Item LIKE '%critical array%'
GROUP BY hosts.HostName,
       clients.Name,
       types.DataStatus,
       datediff(NOW(), scans.Time)
ORDER BY clients.Name;

Collectives™ on Stack Overflow

MySQL query to combine rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related