I tried a different approach which produces:

A1 abc blue:green:yellow
A2 cde red:yellow
B1 xyz green
[download]

Most of this code is in making the SQLite DB. My "SQL kung-foo" is pretty weak. I think better SQL would be superior. But I got the unique values of Column 1 using the DISTINCT SQL keyword. Then I used those unique values in a SELECT statement loop to get unique rows which filters out the duplicate col3 values, then I printed the results in the same loop.

I suppose that there are many variations on this theme. You could just get the unique rows in a single SQL statement and then use Perl to organize the printout by Column 1.

I decided to go ahead and post this "weak" solution because it has a few examples of how to work with a reference to an Array of Array, which is one of the ways that you can access the results from the DBI.

Update: I went ahead and coded my own suggestion of using one SELECT statement for the distinct rows and using Perl to organize the printout. This works much better and produces the same result:

# Ok,  with SELECT DISTINCT * and using Perl for Printout

$sth= $dbh->prepare ('SELECT DISTINCT * from test');
$sth -> execute;
my $ref_distinct_rows = $sth->fetchall_arrayref;

my %result;
foreach my $row (@$ref_distinct_rows)
{
   push (@{$result{"$row->[1] $row->[2]"}}, $row->[3]);
}

foreach my $key (sort keys %result)
{
   print "$key ",join (':',sort @{$result{$key}}),"\n";
}
[download]

I think the post, Re: join column 3 for unique values from NetWallah++ is the best direction to go.
Try to get the DB to do as much as possible.
SQLite doesn't have stored procedures so I couldn't do that. However Sybase does and you should consider that. If the SQL is "right", then there will be only one Perl loop that just prints the results from the DB.

==end update

#!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
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.