#!/usr/bin/perl -w use strict; my @values = ( [ qw( pers dev sales ) ], # dept [ qw( consultant contractor employee ) ], # category [ qw( m f ) ] # gender ); my @condition_names = ('dept', 'category', 'gender'); my $operation = 'SUM'; my $field_to_operate = 'salary'; # permute function written by Randal L. Schwartz, # aka merlyn # http://www.perlmonks.org/index.pl?node_id=24270 sub permute { my $last = pop @_; unless (@_) { return map [$_], @$last; } return map { my $left = $_; map [@$left, $_], @$last } permute(@_); } my @permuted = permute(@values); #now @permuted = ( # ['pers', 'consultant', 'm'], # ['pers', 'consultant', 'f'], # ['pers', 'contractor', 'm'], # ['pers', 'contractor', 'f'], # ['pers', 'employee', 'm'], # ['pers', 'employee', 'f'], # ['dev', 'consultant', 'm'], # ['dev', 'consultant', 'f'], # ['dev', 'contractor', 'm'], # ['dev', 'contractor', 'f'], # ['dev', 'employee', 'm'], # ['dev', 'employee', 'f'], # ['sales', 'consultant', 'm'], # ['sales', 'consultant', 'f'], # ['sales', 'contractor', 'm'], # ['sales', 'contractor', 'f'], # ['sales', 'employee', 'm'], # ['sales', 'employee', 'f'], #); my $query = qq{SELECT country, loc\n}; for my $line(@permuted) { #my @flds = @condition_names; my $ndx=0; $query .= "," . $operation . "(CASE WHEN " . join(" AND ", map { $condition_names[$ndx++] . " = '" . $_ . "'" } @$line) . " THEN " . $field_to_operate . " ELSE NULL END ) AS " . "'" . join ("#", @$line) . "'\n" } $query .= ",$operation($field_to_operate) AS total\n" . qq{FROM person INNER JOIN depts ON (person.dept_id=depts.dept_id) INNER JOIN categories ON (person.cat_id=categories.cat_id) INNER JOIN locs on(locs.loc_id=person.loc_id) INNER JOIN countries ON(locs.country_id=countries.country_id)\n} . "GROUP BY country, loc\n"; print $query; #### my $depts = $dbh->selectcol_arrayref(qq{SELECT DISTINCT dept FROM depts}) or die "can't get dept values\n"; my $categories = $dbh->selectcol_arrayref(qq{SELECT DISTINCT category FROM categories}) or die "can't get category values\n"; my $genders = $dbh->selectcol_arrayref(qq{SELECT DISTINCT gender FROM person}) or die "can't get gender values\n"; my @values = ( $depts, $categories, $genders ); #### $query = qq{ SELECT country, loc ,SUM(CASE WHEN dept = 'pers' AND category = 'consultant' AND gender = 'm' THEN salary ELSE NULL END ) AS 'pers#consultant#m' ,SUM(CASE WHEN dept = 'pers' AND category = 'consultant' AND gender = 'f' THEN salary ELSE NULL END ) AS 'pers#consultant#f' ,SUM(CASE WHEN dept = 'pers' AND category = 'contractor' AND gender = 'm' THEN salary ELSE NULL END ) AS 'pers#contractor#m' ,SUM(CASE WHEN dept = 'pers' AND category = 'contractor' AND gender = 'f' THEN salary ELSE NULL END ) AS 'pers#contractor#f' ,SUM(CASE WHEN dept = 'pers' AND category = 'employee' AND gender = 'm' THEN salary ELSE NULL END ) AS 'pers#employee#m' ,SUM(CASE WHEN dept = 'pers' AND category = 'employee' AND gender = 'f' THEN salary ELSE NULL END ) AS 'pers#employee#f' ,SUM(CASE WHEN dept = 'dev' AND category = 'consultant' AND gender = 'm' THEN salary ELSE NULL END ) AS 'dev#consultant#m' ,SUM(CASE WHEN dept = 'dev' AND category = 'consultant' AND gender = 'f' THEN salary ELSE NULL END ) AS 'dev#consultant#f' ,SUM(CASE WHEN dept = 'dev' AND category = 'contractor' AND gender = 'm' THEN salary ELSE NULL END ) AS 'dev#contractor#m' ,SUM(CASE WHEN dept = 'dev' AND category = 'contractor' AND gender = 'f' THEN salary ELSE NULL END ) AS 'dev#contractor#f' ,SUM(CASE WHEN dept = 'dev' AND category = 'employee' AND gender = 'm' THEN salary ELSE NULL END ) AS 'dev#employee#m' ,SUM(CASE WHEN dept = 'dev' AND category = 'employee' AND gender = 'f' THEN salary ELSE NULL END ) AS 'dev#employee#f' ,SUM(CASE WHEN dept = 'sales' AND category = 'consultant' AND gender = 'm' THEN salary ELSE NULL END ) AS 'sales#consultant#m' ,SUM(CASE WHEN dept = 'sales' AND category = 'consultant' AND gender = 'f' THEN salary ELSE NULL END ) AS 'sales#consultant#f' ,SUM(CASE WHEN dept = 'sales' AND category = 'contractor' AND gender = 'm' THEN salary ELSE NULL END ) AS 'sales#contractor#m' ,SUM(CASE WHEN dept = 'sales' AND category = 'contractor' AND gender = 'f' THEN salary ELSE NULL END ) AS 'sales#contractor#f' ,SUM(CASE WHEN dept = 'sales' AND category = 'employee' AND gender = 'm' THEN salary ELSE NULL END ) AS 'sales#employee#m' ,SUM(CASE WHEN dept = 'sales' AND category = 'employee' AND gender = 'f' THEN salary ELSE NULL END ) AS 'sales#employee#f' ,SUM(salary) AS total FROM person INNER JOIN depts ON (person.dept_id=depts.dept_id) INNER JOIN categories ON (person.cat_id=categories.cat_id) INNER JOIN locs on(locs.loc_id=person.loc_id) INNER JOIN countries ON(locs.country_id=countries.country_id) GROUP BY country, loc } #### @permuted = ( ['pers', 'consultant', 'm'], ['pers', 'consultant', 'f'], ['pers', 'consultant'], ['pers', 'contractor', 'm'], ['pers', 'contractor', 'f'], ['pers', 'contractor'], ['pers', 'employee', 'm'], ['pers', 'employee', 'f'], ['pers', 'employee'], ['pers'], ['dev', 'consultant', 'm'], ['dev', 'consultant', 'f'], ['dev', 'consultant'], ['dev', 'contractor', 'm'], ['dev', 'contractor', 'f'], ['dev', 'contractor'], ['dev', 'employee', 'm'], ['dev', 'employee', 'f'], ['dev', 'employee'], ['dev'], ['sales', 'consultant', 'm'], ['sales', 'consultant', 'f'], ['sales', 'consultant'], ['sales', 'contractor', 'm'], ['sales', 'contractor', 'f'], ['sales', 'contractor'], ['sales', 'employee', 'm'], ['sales', 'employee', 'f'], ['sales', 'employee'], ['sales'], ); #### $header_formats = [ [ { name => 'Area', colspan => 1, rowspan => 3 }, { name => 'country', colspan => 1, rowspan => 3 }, { name => 'location', colspan => 1, rowspan => 3 }, { name => 'pers', colspan => 6, rowspan => 0 }, { name => 'sales', colspan => 7, rowspan => 0 }, { name => 'dev', colspan => 5, rowspan => 0 }, { name => 'total', colspan => 1, rowspan => 3 } ], [ { name => 'employee', colspan => 3, rowspan => 0 }, { name => 'contractor', colspan => 2, rowspan => 0 }, { name => 'total', colspan => 1, rowspan => 2 }, { name => 'employee', colspan => 2, rowspan => 0 }, { name => 'contractor', colspan => 2, rowspan => 0 }, { name => 'consultant', colspan => 2, rowspan => 0 }, { name => 'total', colspan => 1, rowspan => 2 }, { name => 'employee', colspan => 2, rowspan => 0 }, { name => 'consultant', colspan => 2, rowspan => 0 }, { name => 'total', colspan => 1, rowspan => 2 } ], [ { name => 'f', colspan => 1, rowspan => 1 }, { name => 'm', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 }, { name => 'm', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 }, { name => 'm', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 }, { name => 'm', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 }, { name => 'f', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 }, { name => 'm', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 }, { name => 'f', colspan => 1, rowspan => 1 }, { name => 'total', colspan => 1, rowspan => 1 } ] ]; #### # $level =0; $value = 'N'; print '', $value,''; #### $recs_formats = { 0 => { 'N' => [ 7 ], 'S' => [ 3 ], 'zzzz' => [ 1 ], }, 1 => { 'Germany' => [ 4 ] 'UK' => [ 2 ], 'Italy' => [ 2 ], 'zzzz' => [ 1, 1, 1 ], }, 2 => { 'Berlin' => [ 1 ], 'Bonn' => [ 1 ], 'Munich' => [ 1 ], 'London' => [ 1 ] 'Rome' => [ 1 ], 'zzzz' => [ 1, 1, 1, 1, 1, 1 ], } }; #### -- xtab { colspan => 22 } |-- Area { colspan => 1 rowspan => 3 } |-- country { colspan => 1 rowspan => 3 } |-- location { colspan => 1 rowspan => 3 } |-- pers { colspan => 6 } | |- employee { colspan => 3 } | | |- f { colspan => 1 rowspan => 1 } | | |- m { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | |- contractor { colspan => 2 } | | |- m { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | +- total { colspan => 1 rowspan => 2 } |-- sales { colspan => 7 } | | employee { colspan => 2 } | | |- m { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | |- contractor { colspan => 2 } | | |- m { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | |- consultant { colspan => 2 } | | |- f { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | +- total { colspan => 1 rowspan => 2 } |-- dev { colspan => 5 } | |- employee { colspan => 2 } | | |- m { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | |- consultant { colspan => 2 } | | |- f { colspan => 1 rowspan => 1 } | | +- total { colspan => 1 rowspan => 1 } | +- total { colspan => 1 rowspan => 2 } +-- total { colspan => 1 rowspan => 3 } -- recs { rowspan => 11 } |-- N { rowspan => 7 } | |- Germany { rowspan => 4 } | | |- Berlin { rowspan => 1 } | | |- Bonn { rowspan => 1 } | | |- Munich { rowspan => 1 } | | +- zzzz { rowspan => 1 } | |- UK { rowspan => 2 } | | |- London { rowspan => 1 } | | +- zzzz { rowspan => 1 } | +- zzzz { rowspan => 1 } | +- zzzz { rowspan => 1 } |-- S { rowspan => 3 } | |- Italy { rowspan => 2 } | | |- Rome { rowspan => 1 } | | +- zzzz { rowspan => 1 } | +- zzzz { rowspan => 1 } | +- zzzz { rowspan => 1 } +-- zzzz { rowspan => 1 } +- zzzz { rowspan => 1 } +- zzzz { rowspan => 1 }