CODEWITHSUNDEEP
mysqlsqlrotationtranspose
dinesh707
dinesh707

Reputation: 12592

How to rotate a table from MySql

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

Upvotes: 1

Views: 7524

Answers (2)

Naktibalda
Naktibalda

Reputation: 14110

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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;

Upvotes: 3

Related Questions