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.