Hi Tom,
I would like to generate cross-tab report as below


DEPT ANALYST CLERK MANAGER PRESIDENT SALESMAN
ACCOUNTING 0 1300 2450 5000 0
RESEARCH 6000 1900 2975 0 0
SALES 0 950 2850 0 5600



I am using query:

select * from (
select   dept.dname as dept, job , sum(sal) Sal 
from emp inner join dept
on (dept.deptno = emp.deptno)
group by dept.dname, job 
) 
PIVOT ( sum(sal) for (job) in ('ANALYST','CLERK','MANAGER', 'SALESMAN','PRESIDENT'))
;

Although this works great PIVOT forces you to hard code the

 for (job) in ('ANALYST','CLERK','MANAGER', 'SALESMAN','PRESIDENT')

Which kind of sucks because this information is available in DB,
and could have gotten by sub query, which is prohibited.

Is there any workaround (Non XML or Non-procedural)?
In absence of workaround I am using perl code below which generated report above though not very efficiently.

#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect ("dbi:Oracle:XE", "scott", "tiger")
  || die ($DBI::errstr . "\n");

$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
$dbh->{ora_check_sql}  = 0;
$dbh->{RowCacheSize}   = 16;


open (my $fh, '>', "crosstab.txt") || die ("Could not open file $!\n");

#-- Base GROUP BY query, PIVOT part comes later dynamically
my $base_query = "
 select   dept.dname as dept, job , sum(sal) Sal 
 from emp inner join dept
 on (dept.deptno = emp.deptno)
 group by dept.dname, job 
";


#-- This will generate header query to be injected in PIVOT
#-- Essentially comma separated job categories
#-- for brain damaged PIVOT

my $sth = $dbh->prepare($base_query) || die ($DBI::errstr . "\n"); 
$sth->execute();

my @header ;
my $rowcount = 0;

while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
#print "$_\t";
    }
#print "\n";
    push(@header, $row[1]);
}

print "\n";

my @header_uniq = uniq(sort(@header));
my $header_query = join ' ', map { qq/'$_',/ } @header_uniq;
$header_query = "(" .substr($header_query,0, -1) . ")" ;


#print " Header: @header \n";
#print " Unique: @header_uniq \n";
#print " Query : $header_query\n";

my $pivot_query = "
 select * from (
 $base_query
 )
 PIVOT ( sum(sal) for (job) in $header_query)
";

#print "$pivot_query \n";

#-- Run final PIVOT query for Oracle
$sth = $dbh->prepare($pivot_query) || die ($DBI::errstr . "\n");
$sth->execute();


#-- Get columns from query
my $fields = join ("\t", @{ $sth->{NAME_uc}});
$fields =~ s/[']//g; # strip quotes in header columns
print "$fields\n";

print $fh "$fields\n";


while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "0" if !defined($_);
        print "$_\t";
 print $fh  "$_\t";

    }
    print "\n";
    print $fh "\n";
}

print "\n";

END {
 $dbh->disconnect if defined($dbh);
 close($fh);
}

sub uniq {
    my %seen = ();
    my @r = ();
    foreach my $a (@_) {
        unless ($seen{$a}) {
            push @r, $a;
            $seen{$a} = 1;
        }
    }
    return @r;
}

Thank you,
Raj.