G'day Skeeve,

"Is there a better way to achieve this transposition?"

The following code generates a list of unique keys once and doesn't require initialisation of %columns. Note that the source data I've used contains existent and non-existent keys along with defined and undefined values; for future reference, please consider providing more representative data in your OP.

#!/usr/bin/env perl

use strict;
use warnings;

use Data::Dump;

my $data = [
    { a => 1                     },
    { a => 3,     b => 4         },
    {                            },
    {             b => 8         },
    { a => undef,         c => 9 },
];

my (%columns, %seen_key);
my @uniq_keys = grep !$seen_key{$_}++, map keys(%$_), @$data;

for my $row (@$data) {
    push @{$columns{$_}}, $row->{$_} for @uniq_keys;
}

dd \%columns;
[download]

That outputs:

{
  a => [1, 3, undef, undef, undef],
  b => [undef, 4, undef, 8, undef],
  c => [undef, undef, undef, undef, 9],
}
[download]

If you want something other than undef to represent your null values — a zero-length string for instance — you can change the push to this if you have Perl 5.10 or later:

push @{$columns{$_}}, $row->{$_} // '' for @uniq_keys;
[download]

For older versions of Perl, you can use this:

push @{$columns{$_}}, defined($row->{$_}) ? $row->{$_} : '' for @uniq_
+keys;
[download]

Both of those produce identical output:

{
  a => [1, 3, "", "", ""],
  b => ["", 4, "", 8, ""],
  c => ["", "", "", "", 9],
}
[download]

my $sql= new SQL::Abstract( array_datatypes => 1 );
[download]

my $sql = SQL::Abstract::->new(array_datatypes => 1);
[download]