pivot table in mysql

Question

I know how to make a pivot table in mysql (see code example below), but what if the number of columns in the pivot table is very large and I don't want to type 2000 or so tagnames? - Is there a way to have that list generated? Many thanks in advance.

drop table pivot;
create table pivot SELECT time,
       max(if(tagname = 'a', value, null)) AS 'a',
       max(if(tagname = 'b', value, null)) AS 'b',
       max(if(tagname = 'c', value, null)) AS 'c'
  FROM test where tagname in ('a','b','c')
GROUP BY time;
select * from pivot;

Answer 1

You can always create a shell script that does exactly that :-)

#!/bin/sh

mysql -BN test > /tmp/$$_tagnames.tmp <<SQL
select distinct tagname from test; 
SQL

cat > /tmp/$$_create_table.sql <<EOF
drop table if exists pivot;
create table pivot select 
EOF

while read tag; do
    echo "max(if(tagname = '$tag', value, null)) AS '$tag'," >> /tmp/$$_create_table.sql
done < /tmp/$$_tagnames.tmp

cat >> /tmp/$$_create_table.sql <<EOF
time
FROM test 
GROUP BY time;
select * from pivot;
EOF

mysql -Bt test < /tmp/$$_create_table.sql

rm /tmp/$$_create_table.sql
rm /tmp/$$_tagnames.tmp

---

Data:

mysql> select * from test;
+---------+-------+---------------------+
| tagname | value | time                |
+---------+-------+---------------------+
| a       | foo   | 2012-12-21 00:00:01 |
| b       | foo   | 2012-04-27 00:00:01 |
| c       | bar   | 2012-03-27 00:00:01 |
| d       | bar   | 2012-12-21 00:00:01 |
+---------+-------+---------------------+
4 rows in set (0.00 sec)

Script output:

$ ./pivot.sh 
+------+------+------+------+---------------------+
| a    | b    | c    | d    | time                |
+------+------+------+------+---------------------+
| NULL | NULL | bar  | NULL | 2012-03-27 00:00:01 |
| NULL | foo  | NULL | NULL | 2012-04-27 00:00:01 |
| foo  | NULL | NULL | bar  | 2012-12-21 00:00:01 |
+------+------+------+------+---------------------+