On Aug 29, 3:11 am, pie...@hogranch.com (John R Pierce) wrote:
> On 08/28/11 3:13 AM, pe...@vanroose.be wrote:
>
> > First remark: that's an unusual table design: I would have expected the
> > pivoted form of Table2. Why? Well, every time a row is added to Table1
> > (DML),
> > a*column* has to be added to Table2 (DDL).
>
> indeed, that was my initial reaction, it looked like a schema designed
> by a spreadsheet user. It violates the tenets of relational data design.
>
> If the table was properly structured, the required data could be queried
> with a single JOIN, albeit not in the exact output form shown.
>
> --
> john r pierce N 37, W 122
> santa cruz ca mid-left coast
Thanks to Peter and Tiger and suggestion from John
Just posting the solution here
$dbh = DBI->connect($databaseName, $databaseUser, $databasePw)
or die "Connect failed: $DBI::errstr\n";
$sth = $dbh->prepare("SELECT Name FROM currency_acc WHERE
currency='SGD'");
$sth->execute();
my @colnames = ();
while (my @names = $sth->fetchrow_array()) { push @colnames,
$names[0]; }
my $query = 'SELECT ACC';
foreach (@colnames) { $query .= ', ' . $_; }
$query .= ' FROM amount_type';
$sth = $dbh->prepare($query);
$sth->execute();
my @names = @{$sth->{NAME_uc}};
print "@names \n";
while (my @r = $sth->fetchrow_array()) { print "@r\n"; }
Thanks
Eddie
