Letters above names in output

[wkolcz]

I have a list of users ordered by last name. The request has come that the client would like a capital letter (for the last name) above each users grouped by the user's first letter of the last name.

I.E.

A
Alexander, Steve
Anserson, James

B
Baily, Jenny
Bonnet, Mike

Can someone give me a hint on how to do this using the last name to determine if there is a letter. Thanks.

[Dan_Bracuk]

The easiest way is to include the first letter as a field in your query. If your db has string functions, that's the best way. If it doesn't, select some constant as a dummy field and then loop through the query to change that value afterwards.

Once you have the first letter in your query, you can use the group attribute of cfoutput.

[wkolcz]

Here is my Query (Oracle).

I have no place to grab a single letter.

SELECT DISTINCT
ind.individual_id,
mail_name,
first_name,
middle_name,
last_name,
name_suffix,
degrees,
clinical_specialty service_name,
<!---prov.planning_specialty service_name,--->
facdept.um_department,
fellow_name_flag
FROM MS.INDIVIDUAL ind,
MS.provider prov,
pcd.clinical_faculty clinfac,
pcd.view_guide_faculty_department facdept
where ind.individual_id = prov.individual_id
AND ind.individual_id = clinfac.individual_id
AND clinfac.individual_id = facdept.individual_id
AND show_on_pcd_flag = 'YES'
AND facdept.um_department='#searchphys_department#'
AND clinical_specialty is not null
UNION
SELECT DISTINCT
ind.individual_id,
mail_name,
first_name,
middle_name,
last_name,
name_suffix,
degrees,
prov.planning_specialty service_name,
<!---prov.planning_specialty service_name,--->
facdept.um_department,
fellow_name_flag
FROM MS.INDIVIDUAL ind,
MS.provider prov,
pcd.clinical_faculty clinfac,
pcd.view_guide_faculty_department facdept
where ind.individual_id = prov.individual_id
AND ind.individual_id = clinfac.individual_id
AND clinfac.individual_id = facdept.individual_id
AND show_on_pcd_flag = 'YES'
AND facdept.um_department='#searchphys_department#'
AND clinical_specialty is null

[paross1]

Something like this?

SELECT first_letter,
individual_id,
mail_name,
first_name,
middle_name,
last_name,
name_suffix,
degrees,
clinical_specialty service_name,
um_department,
fellow_name_flag
FROM
(SELECT DISTINCT
UPPER(SUBSTR(last_name, 1, 1)) AS first_letter,
ind.individual_id,
mail_name,
first_name,
middle_name,
last_name,
name_suffix,
degrees,
clinical_specialty service_name,
facdept.um_department,
fellow_name_flag
FROM MS.INDIVIDUAL ind,
MS.provider prov,
pcd.clinical_faculty clinfac,
pcd.view_guide_faculty_department facdept
where ind.individual_id = prov.individual_id
AND ind.individual_id = clinfac.individual_id
AND clinfac.individual_id = facdept.individual_id
AND show_on_pcd_flag = 'YES'
AND facdept.um_department='#searchphys_department#'
AND clinical_specialty is not null
UNION
SELECT DISTINCT
UPPER(SUBSTR(last_name, 1, 1)) AS first_letter,
ind.individual_id,
mail_name,
first_name,
middle_name,
last_name,
name_suffix,
degrees,
prov.planning_specialty service_name,
facdept.um_department,
fellow_name_flag
FROM MS.INDIVIDUAL ind,
MS.provider prov,
pcd.clinical_faculty clinfac,
pcd.view_guide_faculty_department facdept
where ind.individual_id = prov.individual_id
AND ind.individual_id = clinfac.individual_id
AND clinfac.individual_id = facdept.individual_id
AND show_on_pcd_flag = 'YES'
AND facdept.um_department='#searchphys_department#'
AND clinical_specialty is null)
ORDER BY first_letter, last_name, first_name, middle_name

Phil

[wkolcz]

Is it possible to reselect the last_name and Alias it after I, somehow, trim the column value down to the first letter? Does anyone know how to do that?

[Dan_Bracuk]

Add substr(last_name, 1, 1) first_letter to your query.

By the way, the rest of your query does not meet the requirements you stated in your original post.

[wkolcz]

Thanks for the insight on the trimming. I had found that same solution and it appears to work fine. I am having trouble with the grouping and displaying, but hopefully can clear that up shortly.

What about the query doesn't match my requirements?

[wkolcz]

Stuck.

Ok, I have the first_letter correct and the values of last_name and first_name

How can I list them to have the letter above and the names below?

The way I did this before is not working correctly. Thanks!

[paross1 · Accepted solution]

Something like this:

<table>
<cfoutput query="your_query" group="first_letter>
<tr>#first_letter#</tr>
<cfoutput>
<tr>#last_name#, #first_name#</tr>
</cfoutput>
</cfoutput>
</table>

Phil

[wkolcz]

Ok, the loop seems to work, but I am grabbing many of the single letters so it loops over the alphabet about 2 dozen times. Any way to stop that?

[wkolcz]

Nevermind. Fixed it.