SQLite::VirtualTable::Pivot
  
  # Proof-of-concept implementation.
  # Table with columns named "entity", "attribute", "value".
  
    package Test::Pivot;
    use DBI;
    use DBIx::Simple;
    use Data::Dumper;
    use base 'SQLite::VirtualTable';
    use strict;
    {  my ($sth, @row, @last_row, %queued, $first, $last, $done, $db );
    sub CREATE {
        # Create a new table.
        my ( $class, $module, $caller, $table, $base_table, @opts ) = @_;
        $db ||= DBIx::Simple->connect( "dbi:SQLite:dbname=dbfile.db", "", "" )
          or die DBIx::Simple->error;
        my @columns = ("entity",
         $db->query("select distinct(attribute) from $base_table;")->flat);
        return bless {
            table      => $table,
            base_table => $base_table,
            columns    => \@columns
        }, $class;
    }
    sub DECLARE_SQL {
        # Declare the columns.
        my $self = shift;
        return sprintf "CREATE TABLE %s (%s)",
            $self->{table}, (join ',', @{ $self->{columns} } );
    }
    sub OPEN {
       # Open a cursor
       my $self = shift;
       $sth = $db->dbh->prepare( "select entity,attribute,value from $self->{base_table} order by entity")
         or die $DBI::errstr;
       $sth->execute or die $DBI::errstr;
       $last = !( @row = $sth->fetchrow_array );
       ($done,$first) = (0,1);
       $self->NEXT();
       return 1;
    }
    sub NEXT {
      # Advance the cursor one row.
      return if $done;
      $done = $last;
      while ($first || $row[0]==$last_row[0]) {
        $first             = 0;
        $queued{ $row[1] } = $row[2];
        $queued{entity}    = $row[0];
        @last_row          = @row;
        $last              = !( @row = $sth->fetchrow_array );
        last if $last;
      }
      @last_row = @row;
    }
    sub COLUMN  {
      # Retrieve the value in a column.
      my ($self, $cur, $n) = @_;
      return delete $queued{$self->{columns}[$n]};
    }
    sub EOF {
      # Is the cursor done?
      $done;
    }
    sub ROWID      { }
    sub CLOSE      { }
    sub DISCONNECT { }
    sub BEST_INDEX { (0, "", undef, 0) }
    sub FILTER     { }
    sub DROP       { }
    *DESTROY = \&DISCONNECT;
    *CONNECT = \&CREATE;
    }
    1;