By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,030 Members | 1,227 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,030 IT Pros & Developers. It's quick & easy.

listing data in columns dependent on letter of alphabet

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
<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.


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.