How to convert multiple rows to one?

Question

I have this table:

CREATE TABLE IF NOT EXISTS `usage` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `host_id` int(10) unsigned NOT NULL,
  `time_id` int(10) unsigned NOT NULL,
  `state` enum('LinuxTU','LinuxExt','View','Browser','Idle','Offline') CHARACTER SET latin1 NOT NULL DEFAULT 'Offline',
  PRIMARY KEY (`id`),
  KEY `host_id` (`host_id`),
  KEY `time_id` (`time_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12998 ;

This table stores state infos for different computers.

I actually can get this. Which stores the values I need but not in correct format:

SELECT time_id, state, COUNT(state) statecount
FROM `usage` u
GROUP BY time_id, state

Result:

time_id  state      statecount
      7  LinuxTU            20
      7  LinuxExt           11
      7  View               51
      7  Browser             5
      7  Idle               67
      7  Offline            83
      8  LinuxTU            22
      8  LinuxExt           10
      8  View               55
      8  Browser             4
      8  Idle               66
      8  Offline            80

I want to get a matrix with counted state values out of it like this:

time_id   LinuxTU   LinuxExt   View   Browser   Idle   Offline
      7        20         11     51         5     67        83
      8        22         10     55         4     66        80

How can I get this?

Do you know the six additional columns that you want? Or do these depend on the data? If the first, google "mysql pivot". If the second, google "mysql dynamic pivot". — Gordon Linoff, Feb 17 '15 at 14:16
There's already a stackoverflow question for pivoting data, might help: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Oli, Feb 17 '15 at 15:38

score 0 · Answer 1 · answered Feb 17 '15 at 15:56

OK, pivot table is the key. Perhaps there is a query wihtout subquerys but I didn't find it. And this query is not flexible to column changes. But it works...

SELECT time_id,
    SUM(IF(state='LinuxTU', statecount, NULL)) AS LinuxTU,
    SUM(IF(state='LinuxExt', statecount, NULL)) AS LinuxExt,
    SUM(IF(state='View', statecount, NULL)) AS View,
    SUM(IF(state='Browser', statecount, NULL)) AS Browser,
    SUM(IF(state='Idle', statecount, NULL)) AS Idle,
    SUM(IF(state='Offline', statecount, NULL)) AS Offline
FROM (
    SELECT time_id, state, COUNT(state) statecount
    FROM `usage` u
    GROUP BY time_id, state
) AS subtab
GROUP BY time_id

How to convert multiple rows to one?

1 Answers1