farhan has asked for the wisdom of the Perl Monks concerning the following question:

Hi Guys,
I have Perl DBI oracle script that fetches data from more than one table, output is coming into columns, how i can display these in row rather than in column.
For example output of the script is like as

David
michael
William
Kenny

I want display like as
David,Micheal
William,Kenny


Is it possible?
Code is attached 

#!F:/perl/bin/perl.exe

use DBI;


open(FH,"C:/ehealth/custom/Script/test");

foreach $fin (<FH>) {

my($final) = split(/\n/,$fin);

my $dbh = DBI->connect( 'dbi:Oracle:xxxxxxx','xxxxxx','xxxxxx',{
                        RaiseError => 1,
                        AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::er
+rstr";


my $sql = qq(select c.name from NH_Element a, nh_group_members b, nh_g
+roup c where a.element_id = b.element_id and b.group_id = c.group_id 
+and a.name='$final' ORDER by c.name);

my $sth = $dbh->prepare( $sql );

$sth->execute();

my($alias);

$sth->bind_columns( undef,\$alias);

while($sth->fetchrow_array()) {

print "$alias\n";

}

$sth->finish();

$dbh->disconnect();


}
close(FH);

[download]
test File ias as

S60172C151887-RH
mgr01.beg01-RH

Output is as:

CMS-Monitoring
CUST-RCE
WoG_Routers
gnet-all

Wanting Output as:

CMS-Monitoring,CUST-RCE
WoG_Routers,gnet-all
Thanks,
Farhan

Replies are listed 'Best First'.
Re: PERL DBI Oracle
by Krambambuli (Curate) on May 15, 2007 at 07:09 UTC
    You might want to replace 
    while($sth->fetchrow_array()) {
     print "$alias\n";
}

    [download]
    with something like 
    my $column_counter = 2;

my @grouped_columns = ('') x $column_counter;

my $j = 0;

while( $sth->fetchrow_array() ) {
    $grouped_columns[ $j ] = $alias;
    if (++$j == $column_counter) {
        _process_and_display_full_row( @grouped_columns );
        $j = 0;
        @grouped_columns = ('') x $column_counter;
    }
}

if ($j > 0) {
    _process_and_display_partial_row( @grouped_columns );
}

    [download]
[reply]
[d/l]
[select]
Re: PERL DBI Oracle
by Moron (Curate) on May 15, 2007 at 11:55 UTC
    It looks like a flip-flop ... (update: in Perl you can also use a variable as it's own flag provided there are no empty or zero values for it permitted in the actual data) 
    # ...

$sth->bind_columns( undef,\$alias);
my $key = 0;
while($sth->fetchrow_array()) {

    if ( $key  ) {
        print "$key, $alias\n";
        $key = 0;
    }
    else{
        $key = $alias;
    }
}

    [download]
    or if you later realise you need to store the pairs anyway... ;) 
    
$sth->bind_columns( undef,\$alias);
my %hash;
my $key = 0;
while($sth->fetchrow_array()) {

    if ( $key  ) {
        $hash{ $key } = $alias;
        # print "$key, $alias\n";
        $key = 0;
    }
    else{
        $key = $alias;
    }
}

    [download]
    (updated to get rid of an unnecessary step)
    __________________________________________________________________________________

    ^M Free your mind!

[reply]
[d/l]
[select]
      Thanks a lot Moron, it worked

      Farhan
[reply]
      Hello Moron,

      You code worked but if sql queries returns more than 2 rows , it still shows 2 entries. Is there wany way to change it.

      For example your code returns

      F:\perl\bin>perl C:\eHealth\custom\Script\nodata_l.pl
      BP-ALL-SITES, BP_COCO

      But actual result of query has 3 rows

      BP-ALL-SITES
      BP_COCO
      CMS-Monitoring


      Farhan
[reply]
        Aha, I was interpreting an even number of rows from the OP. I think I would need to know what "works" means to you for an odd number of rows in that case. Update: Or is the three rows an unhandled error condition you are talking about?Also, if there are four rows, how do we know whether they are two pairs or not?
        __________________________________________________________________________________

        ^M Free your mind!

[reply]
Re: PERL DBI Oracle
by chrism01 (Friar) on May 15, 2007 at 06:44 UTC
    Well, you can replace the "\n" with a comma, but it looks like you want a newline every 2nd item, so you'd need a ctr (or a flag) to tell you when to use a newline or a comma.

    Cheers
    Chris

[reply]
      Chris,

      Thanks for reply. Actually if u look at the code, sql query is in the loop, query returns more than one item for every element, I need commas between items for one element and then new line for different elements. i dont know it is possible or not

      Farhan
[reply]

Back to Seekers of Perl Wisdom