#!usr/bin/perl
use warnings;
use strict;

use Data::Dumper;
use DBI qw(:sql_types);

my $dbfile  = "test1171182.sqlite";

if (-e $dbfile) {unlink $dbfile or die "Delete of $dbfile failed! $!\n";}

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1})
    or die "Couldn't connect to database: " . DBI->errstr;

$dbh->do ("CREATE TABLE test
             (  id      integer PRIMARY KEY AUTOINCREMENT,
                col1    varchar(10)   DEFAULT '',
                col2    varchar(10)   DEFAULT '',
                col3    varchar(10)   DEFAULT ''
             );
          ");
          
$dbh->do("BEGIN");  #a single transaction makes this MUCH faster
import_data();
$dbh->do("COMMIT"); 

### Find unique values for Column 1

my $sth = $dbh->prepare('SELECT DISTINCT col1 FROM test');
$sth->execute;
my $ref_unique_col1 = $sth->fetchall_arrayref;        #ref to Array of Arrays
my @unique_col1 = sort map{$_->[0]}@$ref_unique_col1; #first col from AoA

$sth = $dbh->prepare('SELECT DISTINCT * FROM test WHERE col1 IS ?');

### Use unique values for Column one to get AoA of unique col3 values

foreach my $col1 (@unique_col1)
{
   $sth->execute($col1);
   my $ref_result = $sth->fetchall_arrayref;
   my @col3 = map{$_->[3]}@$ref_result;  #get entire column
   #  print Dumper $ref_result; #turn on for debugging...
   print "$ref_result->[0][1] $ref_result->[0][2] ";
   print join (':',sort @col3),"\n";
}

sub import_data
{
   my $add = $dbh->prepare("INSERT INTO test (col1, col2, col3)
                            VALUES(?,?,?)");

   while (my $line = <DATA>)
   {
       next if $line =~ /^\s*$/; #skip blank lines
  
       my ($col1, $col2, $col3) = split ' ', $line;
       $add->execute($col1, $col2, $col3);
   }
}   

=Above Prints:
A1 abc blue:green:yellow
A2 cde red:yellow
B1 xyz green
=cut

__DATA__
A1 abc yellow
B1 xyz green
A2 cde red
A1 abc green
A2 cde yellow
A1 abc blue