gogoglou has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks, I don't know if anyone asked his before, and it might be a trivial question. I want to reverse the rows into columsn and vice versa from a mysql table. If I export everything from the table into a tab delimited file, is there any convenient way to do that? I tried searching in the tutorials but I couldn't find anything. thank you in advance for any suggestions

Replies are listed 'Best First'.
Re: Reversing a mysql table
by moritz (Cardinal) on Apr 07, 2010 at 11:56 UTC
    I want to reverse the rows into columsn and vice versa from a mysql table.

    I'm quite curious why you want to do such a thing - care to tell me? I'm hard pressed to find a use case for it. (It could also be an XY Problem)

    I suggest you search for mysql export to csv. You can likely change the delimiter to a tab if necessary.

    Perl 6 - links to (nearly) everything that is Perl 6.
Re: Reversing a mysql table
by GrandFather (Saint) on Apr 07, 2010 at 12:03 UTC

    Why? If you need to turn the database inside out in such a fashion then maybe your database design is wrong? What is the bigger issue that you are trying to solve?

    True laziness is hard work
Re: Reversing a mysql table
by Anonymous Monk on Apr 07, 2010 at 11:31 UTC
    is there any convenient way to do that?

    Yes, ask the database to SORT the results for you.

      I tried that but it doesn't work. it is a huge table with ~2300 rows and 36 columns. I need to turn it into 2300 columns and 36 rows
        In theory it is possible and Perl could probably through DBI and DBD::mysql slurp in the whole database, transpose it in memory and write it all back to a new table with rows and columns exchanged.

        However, there are certain limits in MySQL (from the v.5.0 docs):

        D.7.2. The Maximum Number of Columns Per Table

        There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors, listed in the following discussion.

        Every table has a maximum row size of 65,535 bytes. This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.

        The maximum row size constrains the number of columns because the total width of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.

        Will your transposed data fit within these limits?

        Another very practical question: what will be the column-names? Is there a way to generate them automatically?


        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

        2300 rows is not huge - millions of rows is huge :-) The transformation should not be a problem in perl, e.g. by reading into an array of arrays with the DBI and then traverse it with reversed indices, printing out a series of "INSERT INTO table VALUES (),(),()..." statements into a sql file. Add a "drop table" statement and you can use this to repopulate your table from the file.

        However, a table with 36 rows and 2300 columns seems very wrong and it might be better to take the opportunity to redesign the database. If you can describe the database it would be easier to give more specific help.

Re: Reversing a mysql table
by jacaril (Beadle) on Apr 07, 2010 at 13:19 UTC

    Unfortunately there is no easy way to transpose data in MySQL itself you will have to do it in code. One simple solution could be to build a hash of the columns, write to them as CSV and merge the results to a file when you've finished reading the rows.

Re: Reversing a mysql table
by JavaFan (Canon) on Apr 07, 2010 at 16:48 UTC
    Print it out, and rotate the paper 90 degrees?
      thanks everyone for the replies. It is mostly though as someone suggested a mater of bad database design, so I am going to change my design, and hopefully this will solve the problem. Thanks again
Re: Reversing a mysql table
by graff (Chancellor) on Apr 09, 2010 at 01:48 UTC
    Here's a script that I use when I want columns turned into rows and vice-versa (assuming tab delimiters for both input and output). The code itself is 4 lines (including the shebang line, which covers most of the work), and the rest is docs.

    This is especially handy when I just want to see a row or two from a table with lots of columns, and the first line of data fed to the script is the column labels.

    #!/usr/bin/perl -l -n @F = split/\t/; $a[$_][$.-1] = $F[$_] for (0..$#F); END{ print join "\t", @{$a[$_]} for (0..$#a) } =head1 NAME transpose-tsv -- invert a tab-delimited table =head1 DESCRIPTION This stdin-stdout filter assumes that the input is two or more lines of plain text consisting of tab-separated-values, and that all lines have a consistent number of fields. (It doesn't do sanity checks.) The output will be transposed (or inverted), so that what had been columns in the input become rows, and vice-versa. Input like this: heading1 heading2 heading3 value11 value12 value13 value21 value22 value23 value31 value32 value33 will be output like this: heading1 value11 value21 value31 heading2 value12 value22 value32 heading3 value13 value23 value33 =head1 AUTHOR David Graff =cut

    Update: forgot to mention (in case it's not obvious): given this little script, the only other thing you need is something that will dump your mysql table content to stdout as one tab-delimited row per line, so you can pipe its output to this script. You probably want that sort of tsv table-dump tool anyway, if you don't have one already.