Create a summary result with one query.

Question

I have a table with the following format.

mysql> describe unit_characteristics;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| uut_id               | int(10) unsigned | NO   | PRI | NULL    |                |
| uut_sn               | varchar(45)      | NO   |     | NULL    |                |
| characteristic_name  | varchar(80)      | NO   | PRI | NULL    |                |
| characteristic_value | text             | NO   |     | NULL    |                |
| creation_time        | datetime         | NO   |     | NULL    |                |
| last_modified_time   | datetime         | NO   |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

each uut_sn has multiple characteristic_name/value pairs. I want to use MySQL to generate a table

+----------------------+-------------+-------------+-------------+--------------+
| uut_sn | char_name_1 | char_name_2 | char_name_3 | char_name_4 | ...          |
+----------------------+-------------+-------------+-------------+--------------+
| 00000  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          | 
| 00001  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          |
| 00002  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          |
| .....  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          |
+----------------------+------------------+------+-----+---------+--------------+

Is this possible with just one query?

Thanks, -peter

Answer 1

This is a standard pivot query:

  SELECT uc.uut_sn,
         MAX(CASE 
               WHEN uc.characteristic_name = 'char_name_1' THEN uc.characteristic_value 
               ELSE NULL 
             END) AS char_name_1,
         MAX(CASE 
               WHEN uc.characteristic_name = 'char_name_2' THEN uc.characteristic_value 
               ELSE NULL 
             END) AS char_name_2,
         MAX(CASE 
               WHEN uc.characteristic_name = 'char_name_3' THEN uc.characteristic_value 
               ELSE NULL 
             END) AS char_name_3,
    FROM unit_characteristics uc
GROUP BY uc.uut_sn

To make it dynamic, you need to use MySQL's dynamic SQL syntax called Prepared Statements. It requires two queries - the first gets a list of the characteristic_name values, so you can concatenate the appropriate string into the CASE expressions like you see in my example as the ultimate query.

Answer 2

You're using the EAV antipattern. There's no way to automatically generate the pivot table you describe, without hardcoding the characteristics you want to include. As @OMG Ponies mentions, you need to use dynamic SQL to general the query in a custom fashion for the set of characteristics you want to include in the result.

Instead, I recommend you fetch the characteristics one per row, as they are stored in the database, and if you want an application object to represent a single UUT with all its characteristics, you write code to loop over the rows as you fetch them in your application, collecting them into objects.

For example in PHP:

$sql = "SELECT uut_sn, characteristic_name, characteristic_value 
        FROM unit_characteristics";
$stmt = $pdo->query($sql);

$objects = array();
while ($row = $stmt->fetch()) {
  if (!isset($objects[ $row["uut_sn"] ])) {
      $object[ $row["uut_sn"] ] = new Uut();
  }
  $objects[ $row["uut_sn"] ]->$row["characteristic_name"] 
                            = $row["characterstic_value"];
}

This has a few advantages over the solution of hardcoding characteristic names in your query:

• This solution takes only one SQL query instead of two.
• No complex code is needed to build your dynamic SQL query.
• If you forget one of the characteristics, this solution automatically finds it anyway.
• GROUP BY in MySQL is often slow, and this avoids the GROUP BY.