Group output by letter of the alphabet 2

[neofactor]

Quick questions...


How do I have CFOUTPUT group a list of words, so they are grouped by their starting Letter.

Example:

A
-------------------------------
ant
art
apple

b
------------------------
bee
bad
boat


You get the idea

I am using access but would like to know how to do it in MS SQL as well.

Thanks.
David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[neofactor]

ohh.. and the solution has to be for cf 4.5
David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[webmigit]

The only database suitable to test this on I have is a bible database, so I hope this works alright for you..

The output is basic but easily modifiable and if CF 4.5 supports the group attribute of cfquery then this should work just fine...

If not I have concocted a much longer solution that would probably work in CF 1, lol..

Oh yes, this code is written for Access 2000 db format... though its basic enough that I suspect it'd work in most anything...

Here is the code... sample output of such, follows it.

<CFQUERY name=&quot;i&quot; datasource=&quot;#request.dsn#&quot;>
 SELECT Left(BookTitle,1) as BT, BookTitle FROM KJV
 ORDER BY BookTitle
</CFQUERY>

<CFOUTPUT query=&quot;i&quot; group=&quot;BT&quot;>
#BT#<BR>
 <CFOUTPUT group=&quot;BookTitle&quot;>    #BookTitle#<BR></CFOUTPUT></CFOUTPUT>

Output:
A
    Acts
    Amos
C
    Colossians
D
    Daniel
    Deuteronomy
E
    Ecclesiastes
    Ephesians
    Esther
    Exodus
    Ezekiel
    Ezra
G
    Galatians
    Genesis
H
    Habakkuk
    Haggai
    Hebrews
    Hosea

 

[neofactor]

Thanks webmigit....
For this and the other thing....

I almost had it I was trying to do:

Distinct Left([Name],1)

But now I see the distinct is already handled with the cfoutput group.

Boy your tip was helpful....
I will click that link below saying so.

See ya later! David McIntosh

http://www.neofactor.com

Have a nice day!

 

[webmigit]

Welcome on both...

Have you tried it in MySQL? I'm curious to know if it works that way... I'd think it would

Tony ALFII.com

http://www.alfii.com

 

[webmigit]

If you would like a count of how many items for each letter... try this...

before the cfoutput...

<CFSET btList=valuelist(i.BT)>

then change:

#BT#<BR>

to

#BT# ---- #ListValueCount(btList,bt)#<BR>

ALFII.com

http://www.alfii.com