how to output this using cfoutput group? 1

[kuolung]

I have a query result as followed:

ID, STATE, ARTICLE
1 AK bug
1 AK plant
1 CA bug
1 CA plant
2 NY fly
.
.

how to do I output the STATE column output when it's a multi-state and separate each state by a comma? i want to output like this:

STATE ARTICLE
AK, CA bug
plant
NY fly
.
.


here is i got so far:
<cfset tmpID="">
<table>
<cfoutput query="myQuery" group="ID">
<cfoutput group="STATE">
<tr>
<td>#STATE#</td>
<td><cfif tmpID NEQ myQuery.ID>#ARTICLE#</td>
</tr>
<cfset tmpID = myQuery.ID>
</cfoutput>
</cfoutput>

but i got this
STATE ARTICLE
AK bug
plant
CA
NY fly
.
.

 

[r937]

what's the ORDER BY in your query?

by the way, you only need one CFOUTPUT with GROUP=

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[kuolung]

i have "ORDER BY id, state, article"

yeah, i just changed to one CFOUTPUT with GROUP=STATE

but how to i output when it's a multi-state, again like this:

STATE ARTICLE
AK, CA bug
plant

thanks!! please help!

 

[r937]

my mistake, you do need two GROUP= cfoutputs

if you have ORDER BY id,state,article, then you need to change this to ORDER BY id,article,state

then use the following

<table>
<cfoutput query="myQuery" group="is">
<cfoutput group="article">
<cfset tmpstates="">
<cfoutput>
<cfset tmpstates
=ListAppend(tmpstates.myQuery.state)>
</cfoutput>
<tr>
<td>#tmpstates#</td>
<td>#myQuery.article#</td>
</tr>
</cfoutput>
</cfoutput>


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[kuolung]

THANKS r937!!! IT WORKS! THANK YOU SO MUCH !!!

 

[ecobb]

Have a star Rudy, you earned it!

Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

thanks

typo in first GROUP=, it should be GROUP="id"

also, i forgot to close my <table>!!

(and me a so-called web developer)



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ecobb]

Well, then...where's the 'Star Retractor'?



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher