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] }
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;
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],
}