asked on

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.

ASKER CERTIFIED SOLUTION

smaglio81

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

smaglio81

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

James Rodgers

what type of db?

smaglio81

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