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


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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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


James RodgersWeb Applications DeveloperCommented:
what type of db?
I don't know which way to go on this one. Your guess is probably better than mine.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.