[sqlite] Sparse matrix

[T&B]

Hi all,

OK, this one has me stumped. I've been staring at it too long.
I have a table with a column like this:

Code
a
a
a
a
b
b
c
c
c

I want to output two columns, one showing the Code, another showing  
the count of each code, but with a null wherever it was a repeat,  
like this:

Code   Count
a      4
.
.
.
b      2
.
c      3
.
.

(Note I've used . to show a null)

Any ideas?

I can simply do this to show the count of each, but it doesn't show  
the blank rows:

select Code, count(*) as Count from List group by Code;

Here's the test table:

create table List( Code text );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'b' );
insert into List values( 'b' );
insert into List values( 'c' );
insert into List values( 'c' );
insert into List values( 'c' );

Thanks,
Tom


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------