Creating an Alphabetical Indexed List (ColdFusion + Microsoft SQL Server)

Question

I'm currently struggling to find anyone who knows how this can be done? I've tried a few different methods and ended up with halfway results but not quite what i wanted. Basically i'm trying to create a list showing all the bands A-Z, but the band names are being called from a database, so i'm having to use #band_name# within a nested list. If i re-write the code and post it, someone might be able to see where i'm going wrong.

<cfoutput query="bandNameList">
  <cfloop from="65" to="90" index="i">
    <UL>
     <LI> #chr(i)#
       <UL>
         <LI> #band_name# </LI>
       </UL>
     </LI>
    </UL>
  </cfloop>
</cfoutput>

Do you want to display letters for which there is no matching band? — Dan Bracuk, Dec 05 '12 at 17:43

barnyr · Accepted Answer · 2012-12-06T10:07:30.837

What I think you're after is to only output the Letter for the first band that begins with that letter. One way to achieve this is to change your query slightly (note I'm using what I think is SQL-92 syntax here, but there's probably a nicer way to get the first letter in your particular database):

select 
  band_name, 
  SUBSTRING(band_name from 1 for 1) AS first_letter
from 
  bands 
order by 
  band_name

Which will get you the first letter in the query.

If you want to group all the bands with numeric first letters together, then you can use SQL's CASE statement to do that (you may need to find the equivalent to ascii() in your DBMS). You could also invert the logic and match against 'normal' letters and lump everything else into a '0-9 and punctuation' category if that's easier. I think that's what a number of music systems do (I'm thinking iTunes on the iPhone, but I'm sure there are others)

select 
  band_name, 
  CASE  
    WHEN ascii(left(band_name, 1)) BETWEEN 48 AND 57 THEN '0-9'
    ELSE left(band_name, 1)
  END AS first_letter   
from 
  bands 
order by 
  band_name

Now you can use that extra column along with cfoutput's group attribute to help get the output as you want it.

<UL>
<cfoutput query="bandNameList" group="first_letter">
     <LI> #first_letter#
     <UL>
       <cfoutput>             
           <LI> #band_name# </LI>             
       </cfoutput>
       </UL>
     </LI>        
</cfoutput>
</UL>

Worked brilliantly, needed a slight tweak from SUBSTRING to Left(band_name, 1) AS first_letter but it's worked fine. Only problem I have now is getting the bands that start with numbers, listed as 0-9 instead of random numbers appearing. — LeeB, Dec 06 '12 at 09:36
Hopefully the edit above will cover the 0-9 case. I'd encourage you to look at Leigh's solution too, as that neatly covers the situation where there is no band starting with the letter 'q'. — barnyr, Dec 06 '12 at 10:11

Matt Busche · Answer 2 · 2012-12-05T17:20:13.760

4

Update your query to have a left(band_name,1) AS BandStart and make sure to order by band_name in your ORDER BY. Then use group to output the list.

<cfoutput query="bandNameList" group="BandStart">
 <UL>
  <LI>#bandNameList.BandStart#
   <cfoutput>
    <UL>
     <LI> #bandNameList.band_name# </LI>
    </UL>
   </cfoutput>
  </LI>
 </UL>
</cfoutput>

edited Dec 05 '12 at 17:20

answered Dec 05 '12 at 17:05

Matt Busche

14,216
5
36
61

Leigh · Answer 3 · 2012-12-05T20:20:14.180

If you want to display all letters, even if no bands starting with that letter exist, another option is using a CTE to generate a table of letters A-Z. Then display the results with a "grouped" cfoutput:

<cfquery name="getBandNameList" ...>
    ;WITH ltrs ( code ) AS (
        SELECT  ascii('A') AS code 
        UNION ALL           
        SELECT  code + 1
        FROM    ltrs
        WHERE   ascii('Z') > code
    )
    SELECT char(code) AS letter, t.band_name
    FROM   ltrs LEFT JOIN @YourTable t ON t.band_name LIKE char(code) +'%'
    ORDER BY letter ASC, t.band_name ASC
</cfquery>

<cfoutput query="bandNameList" group="Letter">
    <UL>
     <LI> #letter#
       <UL>
         <cfoutput>
            <LI> #band_name# </LI>
         </cfoutput>
       </UL>
     </LI>
    </UL>
</cfoutput>

@Dan - Thanks for the syntax correction. I went to approve the edit, but someone had already rejected it... Anyway, it is fixed now. — Leigh, Dec 05 '12 at 20:24

score 1 · Answer 4 · answered Dec 05 '12 at 17:38

Another approach is to write a sql server stored proc that returns everything in a single query object. The code would resemble this:

declare @thisNum as int
declare @lastNum as int
set @thisNum = 65;
set @lastNum = 90;

declare @letters as table(letter char(1))

while (@thisNum <= @lastNum)
begin
insert into @letters values (CHAR(@thisNum))
set @thisNum = @thisNum + 1;
end

select letter, bandname
from @letters left join band on letter = left(bandname, 1)
order by letter, bandname

Then, in ColdFusion, you can use cfoutput with the group attribute.

score 0 · Answer 5 · answered Dec 05 '12 at 17:12

0

Try this code ::

<cfquery datasource="orcl" name="list">
select upper(brand) brand from tbl
</cfquery>
<cfoutput query="list">
  <cfloop from="65" to="90" index="i">
    <UL>
     <LI> #chr(i)#
       <UL>
         <LI><cfif mid(brand,1,1) eq chr(i)> #brand#</cfif></LI>
       </UL>
     </LI>
    </UL>
  </cfloop>
</cfoutput>

answered Dec 05 '12 at 17:12

Ajith Sasidharan

1,155
7
7

1

Seems like that would repeat A-Z for every record in the query? – Leigh Dec 05 '12 at 17:19
this would output 26 lines for each row of the query and also a blank `
` line for each result that wasn't a match.

Matt Busche

Dec 05 '12 at 17:19

Sorry for misunderstanding it..my assumption was to show the list from A to Z and the values corresponding to them...Thanks for the pointers Leigh and Matt – Ajith Sasidharan Dec 05 '12 at 17:22

Creating an Alphabetical Indexed List (ColdFusion + Microsoft SQL Server)

5 Answers5

Linked