How to rotate a table from MySql

Question

Here is my current table

╔══════╦═══╦═══╦═══╦═════╦════╗
║ YEAR ║ A ║ B ║ C ║ ... ║ ZZ ║
╠══════╬═══╬═══╬═══╬═════╬════╣
║ 1995 ║ 1 ║ 0 ║ 1 ║ ... ║  1 ║
║ 1996 ║ 1 ║ 1 ║ 0 ║ ... ║  0 ║
╚══════╩═══╩═══╩═══╩═════╩════╝

And the table contains around 1000 columns. Now i need to write a code to rotate this table into a more sensible table where i represent values which are denoted as 1. So the resulting table should be like

╔══════╦════════╦═══════╗
║ YEAR ║ COLUMN ║ VALUE ║
╠══════╬════════╬═══════╣
║ 1995 ║ A      ║     1 ║
║ 1995 ║ C      ║     1 ║
║ 1995 ║ ZZ     ║     1 ║
║ 1996 ║ A      ║     1 ║
║ 1996 ║ B      ║     1 ║
╚══════╩════════╩═══════╝

So by googling bit i started creating a SQL like follows

BEGIN
    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_du **???** DEFAULT "";

    DEClARE du_cursor CURSOR FOR SELECT * FROM date_usage;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

    OPEN du_cursor;

    get_du: LOOP

        FETCH du_cursor INTO v_du;

        IF v_finished = 1 THEN 
            LEAVE get_du;
        END IF;

        SHOW v_du; <<---

    END LOOP get_du;

    CLOSE du_cursor; 
END

But the problem is i'm not sure how to specify the v_du to be a column and I'm not sure how I'm going to read the column names. And help would be appreciated.

Thankx

I agree with you. But thats what im trying to do here. But the old table has data which i need to migrate into new structure. — dinesh707, Aug 15 '14 at 12:49
Check out this question: http://stackoverflow.com/questions/4950252/mysql-iterate-through-column-names. You want to iterate through the columns in your table (for each row) and insert the column name and value in a new table. Careful with this new table design - looks very much like a MUCK table. — Luke, Aug 15 '14 at 12:49
See also http://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows — Adrian, Aug 15 '14 at 12:51
See also http://stackoverflow.com/questions/17931391/transpose-pivot-rows-to-columns — Adrian, Aug 15 '14 at 12:54

score 3 · Accepted Answer · answered Aug 15 '14 at 12:47

Th easiest way is with a bunch of union all statements:

select year, 'A' as column, A as value from table where A > 0 union all
select year, 'B' as column, B as value from table where B > 0 union all
. . .
select year, 'ZZ' as column, ZZ as value from table where ZZ > 0;

I would list the columns using information_schema.columns and use Excel to generate the code.

The more efficient way on a large table is use to a cross join:

select year, col,
       (case when col = 'A' then A
             when col = 'B' then B
             . . .
             when col = 'ZZ' then ZZ
        end) as value
from table t cross join
     (select 'A' as col union all select 'B' union all . . . select 'ZZ') cols
having value > 0;

+1 because this is the right solution to the question. I can't help thinking through there's an issue with your schema design if you have to do queries like this though... — Liath, Aug 15 '14 at 12:50

score 1 · Answer 2 · answered Aug 15 '14 at 12:48

If you want to migrate your data to a new table, do it this way:

INSERT INTO new_table(`year`, `column`, `value`) SELECT year, 'A', A FROM oldTable WHERE A > 0;
INSERT INTO new_table(`year`, `column`, `value`) SELECT year, 'B', B FROM oldTable WHERE B > 0;
INSERT INTO new_table(`year`, `column`, `value`) SELECT year, 'C', C FROM oldTable WHERE C > 0;

repeat for all column names.

How to rotate a table from MySql

2 Answers2