MySQL: Convert column into rows

Question

I have a zillion of colums that I need to convert into rows.

I have 3 main categories (CAT, ODB & GPA) Each category has a month (From Jan, to Dec) and each category has a year) Here is a sample of the structure:

CATJAN2002 | CATFEB2002...| CATDEC2002...| ODBJAN2003...| GPAMAR2013

In vba I can create a simple loop, to export this data into another table:

New_Table fields: ID, TYPE, YEAR, MONTH, VALUE . Can YOU PLEASE help me find a way to automatically go thought the table's columns and start inputing the data into the new table? Can this be done in MySQL alone?

I forgot to menton that the combination of the first columns (STORE, ITEM & CUSTOMER ) is the ID (Or primary key) [SELECT CONCAT(STORE, ITEM, CUSTOMER) AS ID

possible duplicate: http://stackoverflow.com/questions/13944417/mysql-convert-column-to-row-pivot-table — David Starkey, Mar 26 '13 at 22:01
How many rows in your original table? within an order of magnitude. — Marc Audet, Mar 26 '13 at 22:02
@MarcAudet I am guessing about 400. If there are 3 categories for 12 months from 2002 - 2013 — Taryn, Mar 26 '13 at 22:03
Write a script and map columns from the old table to the new schema. This is not that hard to do but it is hard to be elegant. — Marc Audet, Mar 26 '13 at 22:06
if its only 400 rows, there's not much need to be elegant, particularly if it's a one-off task. — Spudley, Mar 26 '13 at 22:07
@MarcAudet I have 72 columns that I need them to convert into rows as `ID, type` (The first three characters of the column), `year` (The last characters) and `month` where I need to convert the month into a number — Omar, Mar 26 '13 at 22:24

fthiella · Accepted Answer · 2013-03-26T22:41:50.283

The idea is to use a query like this:

SELECT CONCAT(STORE, ITEM, CUSTOMER) ID,
       'CAT' TYPE, 2002 YEAR, 'JAN' MONTH, CATJAN2002 VALUE
FROM yourtable
UNION ALL
SELECT CONCAT(STORE, ITEM, CUSTOMER) ID,
       'CAT' TYPE, 2002 YEAR, 'FEB' MONTH, CATFEB2002 VALUE
FROM yourtable
UNION ALL
...

And you can make it using a dynamic query, like 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 FROM yourtable')
  SEPARATOR ' UNION ALL ')
FROM information_schema.COLUMNS
where
  table_name = 'yourtable'
  AND (column_name LIKE 'CAT%' OR column_name LIKE 'ODB%' OR column_name LIKE 'GPA%')
INTO @sql;

prepare stm from @sql;
execute stm;

Please see fiddle here.

I forgot to menton that the combination of the first columns (STORE, ITEM & CUSTOMER ) is the ID (Or primary key) [`SELECT CONCAT(STORE, ITEM, CUSTOMER) AS ID` — Omar, Mar 26 '13 at 22:34

MySQL: Convert column into rows

1 Answers1

Linked