cfoutput primary and alternates when each primary does not have an alternate

Posted on 2004-11-14
Last Modified: 2013-12-24

state role fname lname
AL     pri   joe     smith
AL     alt    sally   jones
AK     pri    jim     lee
AR    pri    randy  smythe
CO    pri    lois     gerber
CO    alt    karen   wolf

desired output:

state   primary             alternate
AL     joe     smith        sally   jones
AK     jim     lee
AR     randy  smythe
CO    lois     gerber      karen   wolf

my output looks like this:

state   primary             alternate
AL     joe     smith
AL                               sally   jones
AK     jim     lee
AR     randy  smythe
CO    lois     gerber
CO                              karen   wolf

How can I get the output in the same row by state.

Question by:porterl
    LVL 5

    Accepted Solution

    You can alter the original query to sort by state and role, and then use a group by clause on the output. Kinda like this:

    <cfquery name="qry" datasource="#dsn#">
          SELECT      state, role, fname, lname
          FROM      <tbl>
          SORT BY      state, role DESC

    <cfoutput query="qry" group="state">
                <!--- the state names --->
                <!--- this will loop over all the rows associated with a single state --->
                      <td>#qry.fname# #qry.lname#</td>

    There is another way though.

    LVL 5

    Expert Comment

    You could rewrite the query to make all the results you are looking for appear in a single row, I think it might look something like this.

    <cfquery name="qry" datasource="#dsn#">
          SELECT      t1.state, t1.fname AS priFname, t1.lname AS priLname,
                      t2.fname AS altFname, t2.lname AS altLname
          FROM      <tbl> t1 LEFT OUTER JOIN
                      <tbl> t2 ON t1.state = t2.state
          WHERE      t1.role = 'pri' AND t2.role = 'alt'
          SORT BY      t1.state


    LVL 24

    Expert Comment

    what type of db?
    LVL 5

    Expert Comment

    I don't know which way to go on this one. Your guess is probably better than mine.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    A web service ( is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now