sql - MySQL: Convert column into rows -

i have zillion of colums need convert rows.

i have 3 main categories (cat, odb & gpa) each category has month (from jan, dec) , each category has year) here sample of structure:

catjan2002 | catfeb2002...| catdec2002...| odbjan2003...| gpamar2013

in vba can create simple loop, export data table:

new_table fields: id, type, year, month, value . can please me find way automatically go thought table's columns , start inputing data new table? can done in mysql alone?

i forgot menton combination of first columns (store, item & customer ) id (or primary key) [select concat(store, item, customer) id

the idea use query this:

select concat(store, item, customer) id,        'cat' type, 2002 year, 'jan' month, catjan2002 value yourtable union select concat(store, item, customer) id,        'cat' type, 2002 year, 'feb' month, catfeb2002 value yourtable union ... 

and can make using dynamic query, this:

select   group_concat(   concat(     'select concat(store, item, customer) id,',     '\'',     left(column_name, 3),     '\' type,',     right(column_name, 4),     ' year, \'',     substr(column_name, 4, 3),     '\' month,',     column_name,     ' value yourtable')   separator ' union ') information_schema.columns   table_name = 'yourtable'   , (column_name 'cat%' or column_name 'odb%' or column_name 'gpa%') @sql;  prepare stm @sql; execute stm; 

please see fiddle here.