mysql有没有数据透视功能,mysql中的数据透视表

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;

解决方案

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

#!/bin/sh

mysql -BN test > /tmp/$$_tagnames.tmp <

select distinct tagname from test;

SQL

cat > /tmp/$$_create_table.sql <

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 <

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 |

+------+------+------+------+---------------------+