Pivoting in MySQL - only works on one row at a time?

Question

I'm trying to select assets from RT's database with the the values for a set of custom fields as tables. Relevant tables are as follows:

mysql> describe AT_Assets;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| Type          | int(11)      | NO   | MUL | 0       |                |
| Name          | varchar(200) | NO   | MUL | NULL    |                |
| Description   | varchar(255) | YES  |     | NULL    |                |
| Status        | varchar(20)  | YES  |     | NULL    |                |
| URI           | varchar(255) | YES  |     | NULL    |                |
| LastUpdatedBy | int(11)      | NO   |     | 0       |                |
| LastUpdated   | datetime     | YES  |     | NULL    |                |
| Creator       | int(11)      | NO   |     | 0       |                |
| Created       | datetime     | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)


mysql> describe CustomFields;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name          | varchar(200) | YES  |     | NULL    |                |
| Type          | varchar(200) | YES  |     | NULL    |                |
| RenderType    | varchar(64)  | YES  |     | NULL    |                |
| MaxValues     | int(11)      | YES  |     | NULL    |                |
| Pattern       | text         | YES  |     | NULL    |                |
| Repeated      | smallint(6)  | NO   |     | 0       |                |
| BasedOn       | int(11)      | YES  |     | NULL    |                |
| ValuesClass   | varchar(64)  | YES  |     | NULL    |                |
| Description   | varchar(255) | YES  |     | NULL    |                |
| SortOrder     | int(11)      | NO   |     | 0       |                |
| LookupType    | varchar(255) | NO   |     | NULL    |                |
| Creator       | int(11)      | NO   |     | 0       |                |
| Created       | datetime     | YES  |     | NULL    |                |
| LastUpdatedBy | int(11)      | NO   |     | 0       |                |
| LastUpdated   | datetime     | YES  |     | NULL    |                |
| Disabled      | smallint(6)  | NO   |     | 0       |                |
+---------------+-------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)

mysql> describe ObjectCustomFieldValues;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| CustomField     | int(11)      | NO   | MUL | NULL    |                |
| ObjectType      | varchar(255) | NO   |     | NULL    |                |
| ObjectId        | int(11)      | NO   |     | NULL    |                |
| SortOrder       | int(11)      | NO   |     | 0       |                |
| Content         | varchar(255) | YES  | MUL | NULL    |                |
| LargeContent    | longblob     | YES  |     | NULL    |                |
| ContentType     | varchar(80)  | YES  |     | NULL    |                |
| ContentEncoding | varchar(80)  | YES  |     | NULL    |                |
| Creator         | int(11)      | NO   |     | 0       |                |
| Created         | datetime     | YES  |     | NULL    |                |
| LastUpdatedBy   | int(11)      | NO   |     | 0       |                |
| LastUpdated     | datetime     | YES  |     | NULL    |                |
| Disabled        | smallint(6)  | NO   |     | 0       |                |
+-----------------+--------------+------+-----+---------+----------------+

This query successfully gets me a listing of all assets, but repeats the asset in a separate column for each custom field value:

SELECT AT_Assets.Name, AT_Assets.description, CustomFields.Name, ObjectCustomFieldValues.content FROM ObjectCustomFieldValues inner join CustomFields on ObjectCustomFieldValues.CustomField = CustomFields.id inner join AT_Assets on AT_Assets.id = ObjectCustomFieldValues.ObjectID order by AT_Assets.description;

So I did some reading, and learned how to pivot. Now I have this:

SELECT at_assets.name, 
   at_assets.description AS "Asset Tag", 
   Max(CASE 
         WHEN customfields.name = "make" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Make", 
   Max(CASE 
         WHEN customfields.name = "model" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Model", 
   Max(CASE 
         WHEN customfields.name = "primary user" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Primary User", 
   Max(CASE 
         WHEN customfields.name = "hostname" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Hostname", 
   Max(CASE 
         WHEN customfields.name = "os" THEN objectcustomfieldvalues.content 
       END)              AS "OS", 
   Max(CASE 
         WHEN customfields.name = "purchase date (if known)" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Purchase Date" 
FROM   objectcustomfieldvalues 
   INNER JOIN customfields 
           ON objectcustomfieldvalues.customfield = customfields.id 
   INNER JOIN at_assets 
           ON at_assets.id = objectcustomfieldvalues.objectid 
WHERE  at_assets.id = 5 
ORDER  BY at_assets.description; 

Which works great, when I'm specifying a single asset. However, I would like this to run across every asset. Otherwise I need a Python script to run this hundreds of times manually incrementing the asset id, which is fairly inelegant. How do I go about getting a full list?

Answer 1

you just need group by here. and dont specify the asset name.

change this

  WHERE  at_assets.id = 5 
  ORDER  BY at_assets.description; 

to

   group by at_assets.name
   ORDER  BY at_assets.description; 

Answer 2

You learned everything about the pivot, except adding the group by.

Add the following line after the where:

group by at_assets.name, at_assets.description

To see more than one line, remove or adjust the where clause.

What is happening with your query is the MySQL recognizes it as an aggregation query, because it is using MAX(). There is no group by, so it produces one row -- an aggregation of all the rows.

What about the variables at_assets.name and at_assets.description in the select clause? you might ask. Well, most SQL engines would balk and produce an error. These variables are neither in the group by nor the argument to an aggregation function. MySQL has a (mis)feature called hidden columns that allows such references. However, the values come from arbitrary rows in the source data, so the value are not meaningful unless all values in the group are the same.