comment on

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
[download]

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.

In reply to Re: Reversing a mysql table by graff
in thread Reversing a mysql table by gogoglou

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Clear questions and runnable code get the best and fastest answer
	PerlMonks