Reversing a mysql table

[gogoglou]

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

[moritz]

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.

[GrandFather]

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?

[Anonymous Monk]

is there any convenient way to do that?

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

[gogoglou]

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

[CountZero]

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?

[tospo]

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.

[jacaril]

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.

[JavaFan]

Print it out, and rotate the paper 90 degrees?

[gogoglou]

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

[graff]

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.