First, the definition: It occurs to me that people refer to lots of things as "pivot tables" including the ability to drag certain rows to column headers for filtering and all that datawarehousing stuff.

This isn't that.

I have an unsettlingly frequent need to take "few column, many row" files and query outputs and line them up next to each other. I can't use sql because they are frequently bridges between databases.

and our perl environments are stuck with core modules, and we have a distressing mix of unixen and windows environments, so things like sqlite are out.

What I have here is some code I've banged together that will take a 2-dimensional list (of the "list of list references" variety) and produce a similarly structured list where columns and rows switch places. That is to say this:

1,a
2,b
[download]

will become this:

1,2
a,b
[download]

On it's own, I don't think that would really even bare mention in CB. But what I did add was padding and handling for irregular field counts and blank rows. Meaning that this:

1,2,3

4,5
[download]

(note the leading blank line) will be converted to:

,1,,4
,2,,5
,3,,
[download]

Note that it's not remarkably efficient code. It probably couldn't even be considered particularly 'perlish'. (I find I write remarkably C-ish perl.)

I'm definitely up for suggestions (and recommendations on where to put usage examples, test cases, etc. This is getting a bit long.) The next version (still in frustration) uses external buffering for an intermediate stage and doesn't do the whole schmeggegy in ram.

To use, just call the pivot function with a list of listrefs.

sub pivot
{
    my (@srcgrid) = @_;
    my $max_row = $#srcgrid;
    my $max_col = get_max_cols(@srcgrid);
    my @pivoted;
    for my $col (0..$max_col)
    {
        my @new_row;
        for my $row (0..$max_row)
        {
            my $value;
            if (defined $srcgrid[$row][$col])
            {
                $value = $srcgrid[$row][$col];
            }
            else
            {
                $value = '';
            }
            push @new_row,$value;
        }
        push @pivoted,\@new_row;
    }
    return @pivoted;
}
sub get_max_cols
{
    my (@grid) = @_;
    my $max= 0;
    for my $rowref (@grid)
    {
        my @row = @$rowref;
        if ($max < $#row)
        {
            $max = $#row;
        }
    }
    return $max;
}
[download]

It's not earth shattering code, but I save a lot of time and get a lot of use out of it.

EDIT: Ok, it's not 'pivoting' it's "transposing" (title changed accordingly. Thanks to tye and ambrus for the clarification.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.