• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

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

DB

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.

Thanks.
0
porterl
Asked:
porterl
  • 3
1 Solution
 
smaglio81Commented:
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
</cfquery>

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

There is another way though.

Steven
0
 
smaglio81Commented:
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
</cfquery>

HTH

Steven
0
 
James RodgersWeb Applications DeveloperCommented:
what type of db?
0
 
smaglio81Commented:
I don't know which way to go on this one. Your guess is probably better than mine.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now