Without any missing keys, the transformation is fairly straightforward. You do not need to create an empty array first.

use warnings;
use strict;
use Data::Dump qw(pp);

my $data = [
  {a => 1, b => 2},
  {a => 3, b => 4},
  {a => 5, b => 6},
];

# create data struct like this
#
# my $required = {  a => [1, 3, 5],  b => [2, 4, 6],};

my %required;
foreach my $href (@$data)
{
   push @{$required{$_}}, $href->{$_} foreach keys %$href;
}

pp \%required; #{ a => [1, 3, 5], b => [2, 4, 6] }
[download]

I don't see the requirement for missing keys in the OP? If that is true, then I would probably make 2 passes over $data, the first to discover the "universe of keys" so that appropriate null or undef values can be filled into the arrays as they are generated. There is also the question of what kind of DB you are using and what if any "default" values are specified during table creation? It could also be that SQL::Abstract is not the best way to do what you want?

Update: Some code to implement the above.

### assume that some keys could be missing or others added

my $data2 = [
  {a => 1, },
  {a => 1, b => 2},
  {a => 3, b => 3, c=>7},
  {a => 5, b => 6, c=>8},
  {a => 5, c=>8, d=>9},
];

my %key_universe_h;
my $default =0;

# determine all possible keys (number of columns)

foreach my $href (@$data2)
{
    $key_universe_h{$_}++ foreach keys %$href;
}
my @key_universe_a = sort (keys %key_universe_h);

# proceed as in first example, but with default
# for non-existant key values

my %required2;
foreach my $href (@$data2)
{
   foreach my $column (@key_universe_a)
   {
       my $value = $default;
       $value = $href->{$column} if exists $href->{$column};
       push @{$required2{$column}}, $value;
   }
}
pp $data2;
pp \%required2;
[download]

Prints:

[
  { a => 1 },
  { a => 1, b => 2 },
  { a => 3, b => 3, c => 7 },
  { a => 5, b => 6, c => 8 },
  { a => 5, c => 8, d => 9 },
]
{
  a => [1, 1, 3, 5, 5],
  b => [0, 2, 3, 6, 0],
  c => [0, 0, 7, 8, 8],
  d => [0, 0, 0, 0, 9],
}
[download]

---

---

• 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.