perl/sql server query "re-ordered"

Question

Sorry for asking this question I am sure this was already answered somewhere else but I don't even know what keywords or how to explain it properly... So... I have this perl code that connects to sql server..

my $user = '';
my $pass = '.';
my $connection = "dbi:Sybase:server=;database=Adventure";
my $dbh_SQL = DBI->connect($connection, $user, $pass) or die 'Cannot connect';
my $query_cust_main = "
SELECT BusinessEntityID,
       Title,
       FirstName,
       LastName,
       Suffix,
       JobTitle

FROM HumanResources.vEmployee
order by JobTitle";

my $query_cust_sth = $dbh_SQL->prepare($query_cust_main);
$query_cust_sth->execute();
my $all_cust = $dbh_SQL->selectall_arrayref($query_cust_main, {Slice => {}});

foreach my $data (@$all_cust){
print $data->{BusinessEntityID}." ".$data->{FirstName}." ".$data->{LastName}." ".$data->{JobTitle}."\n";
}

This code prints: 12 Thierry D'Hers Tool Designer 13 Janice Galvin Tool Designer 2 Terri Duffy Vice President of Engineering 25 James Hamilton Vice President of Production 273 Brian Welcker Vice President of Sales

Now, what i want to do...somehow is to re-order the results as this:

Vice President of Production

25 James Hamilton

273 Brian Welcker

Tool Designer

13 Janice Galvin

12 Thierry D'Hers

As in

JobTitle

BusinessEntityID FirstName LastName ...

Is is possible to obtain this re order only in perl? without using/altering the query?

Thank you, R.

Answer 1

Since the query is returning the data sorted by job title, all you have to do is keep track of what the previous job title was and when it is different to the current one, print out a new job title header like this code snippet demonstrates.

my $last_job_title;
foreach my $data (@$all_cust) {
    if($last_job_title ne $data->{JobTitle}) {
        $last_job_title = $data->{JobTitle};
        print $data->{JobTitle},"\n";
    }
    print $data->{BusinessEntityID}." ".$data->{FirstName}." ".$data->{LastName}."\n";
}