listing data in columns dependent on letter of alphabet

[em_lom]

I have a simplelist of names that I got from the database. I have
written all the names from the simple list to the screen.
what I'm looking to do is the following:
A-H I-P Q-Z
anne isabella rachel
beth jen steph
. . .
. . .
. . .
how do I take a simple list of every name and put it under it's
appropriate letter column?

[John Winterbottom]

<em****@yahoo.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I have a simplelist of names that I got from the database. I have
written all the names from the simple list to the screen.
what I'm looking to do is the following:
A-H I-P Q-Z
anne isabella rachel
beth jen steph
. . . how do I take a simple list of every name and put it under it's
appropriate letter column?

SQL is not very good at this sort of thing - but you could do it something
like this.

First your names table

create table names(persName varchar(50) not null primary key)
insert into names(persName) values ('anne')
insert into names(persName) values ('beth')
insert into names(persName) values ('isabella')
insert into names(persName) values ('jen')
insert into names(persName) values ('rachel')
insert into names(persName) values ('steph')

then you need a table for the bins:

create table bins(binName char(3) not null, binStart char(1) primary key,
binEnd char(1))
insert into bins(binName,binStart,binEnd) values('A-H','A','H')
insert into bins(binName,binStart,binEnd) values('I-P','I','P')
insert into bins(binName,binStart,binEnd) values('Q-Z','Q','Z')

Now, here is a query that will give you a ranking for each name within it's
own bin:

select A.persName, A.binName, count(*) as rank
from
(
select n.persName, b.binName
from names n inner join bins b on n.persName >= b.binStart
and n.persName <= b.binEnd
) as A
inner join
(
select n.persName, b.binName
from names n inner join bins b on n.persName >= b.binStart
and n.persName <= b.binEnd
) as B
on A.binName = B.binName
and A.persName >= B.persName
group by A.persName, A.binName

You end up with a resultset that looks like this:

anne A-H 1
beth A-H 2
isabella I-P 1
jen I-P 2
rachel Q-Z 1
steph Q-Z 2

(the formatting may get messed up). Finally, you can feed this into a
crosstab query to get the grid you need.