Transfer dynamic many-column labeled data from MySQL to Excel-like layout - MySQL Pivot Table

Excel is a de facto tool for day-to-day matters inside the lab (unfortunately). Say you are collecting information from several sources (or better you have automated that process into a multithreaded crawler) and curating them into one beautiful excel sheet. One problem that may occur quite early is that you end up with a huge excel sheet, and as result your biologist will start going insane or ask you to link an item to something in the database. Hence, the next logical step is to transfer your information into a relational database.

An example of data source: http://www.ncbi.nlm.nih.gov/gene/2218,

enter image description here

"Official Symbol", "Locus tag" being the labels.

mysql> describe itdb_items;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| itdb_item_id   | int(8)  unsigned | NO   | PRI | NULL    | auto_increment |
| item_name      | varchar(256)     | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

describe itdb_labels;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| itdb_label_id   | int(8)  unsigned | NO   | PRI | NULL    | auto_increment |
| label           | varchar(256)     | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

describe itdb_information;
+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| itdb_information_id   | int(8)  unsigned | NO   | PRI | NULL    | auto_increment |
| itdb_item_id          | int(8)  unsigned | YES  | MUL | NULL    |                |
| itdb_label_id         | int(8)  unsigned | NO   | MUL | NULL    |                |
| information           | text             | YES  |     | NULL    |                |
+-----------------------+------------------+------+-----+---------+----------------+

There are some labels defined for each item type that can be different for each row of data (dynamic, and sometimes not at all present). After struggling a bit here is the query that would create an excel-sheet-style of your data in column-based layout:

SET @sql = NULL;SET @@group_concat_max_len = 9999999;

^ This line tells MySQL not to trim the long group_concats (saves sanity)

DROP TABLE IF EXISTS tempTable1;

select GROUP_CONCAT(DISTINCT concat(' max(case when info.itdb_label_id = ',
convert(itdb_label_id,char),
' then information else NULL end) as `',
label,
'`')
)

INTO @sql
FROM `itdb_labels`;

SET @sql = CONCAT(
'
CREATE TABLE tempTable1
select item.itdb_item_id, item_name, source_link, ', @sql, '
from
itdb_items as item    
left join itdb_information as info on item.itdb_item_id = info.itdb_item_id

group by item.itdb_item_id, item.item_name
order by item.itdb_item_id asc
');

select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

The above query will generate the following output:

enter image description here

Now you can safely hand this excel output to your lab people.