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.
| [reply] |
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
| [reply] |
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. | [reply] |
|
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
| [reply] |
|
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?
CountZero 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
| [reply] |
|
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.
| [reply] |
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.
| [reply] |
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? | [reply] |
|
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
| [reply] |
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. | [reply] [d/l] |