You mean like:
Berm Block Cast-in-place Concrete Combination Concrete 2013 30 172 51 16 0
Exactly! I have four tables that need to be like this. One for area, one for cost, and one for height, and one for avg noise reduction. But once I figure one out, I figure them all out.
getMetaData() might be of use. Let's say your query is called "myQuery".
<cfset thisQueryMeta = getMetaData(myQuery) /> <cfset tqmLen = ArrayLen(thisQueryMeta) /> <!--- I prefer using table-less design, but for simplicity, I'm using a table ---> <table> <tr> <td>&nbsp;</td><cfloop index="idx" from="1" to="#val(tqmLen)#"><td>#thisQueryMeta.name[val(idx)]#</td></cfloop> </tr> <tr> <cfoutput query="myQuery"><td>#myQuery.year#</td><td>#myQuery.col1#</td><td>#myQuery.col2#</td><td>#myQuery.col3#</td></cfoutput> </tr> </table>
Any questions? (Line six is supposed to be a non-breaking space, but this stupid JIVE interface won't let me use that.)
Ok, so first things first, #val(tqmLen)# = 3, so I get 3 loops. I think it's counting columns, and what I need it to count (I think) is distinct instances of material.
On like 9, #myquery.yrorigin# gives me 0, as does any other number I plug into the [ ] (yrorigin is my year column)
But curiously, #myquery.material# gives me "Precast Concrete", so I think we're on the right path. I'm going to fiddle a bit with this. If only I could pull the maximum number for the [ ] I could do a loop!
So, a tiny but of progress. This gives me a list of column headings:
<table> <th scope="col">Year</th> <cfloop list="#listremoveduplicates(valuelist(qgetmaterialarea.material))#" index="m" delimiters=","> <th scope="col"><cfoutput>#m#</cfoutput></th> </cfloop> </tr> ...
Now I just need to figure out how to get the areas to match up with the materials.
1 person found this helpful
So I played around with this a little and this is what I cam up with. There is probably a better way to accomplish this but it works. It take into account the possibility each year doesn't have the same data.
<cfquery name="getData" datasource="demo" > select year, material, area from sample order by year </cfquery> <cfset materialList = listremoveduplicates(valuelist(getData.material))> <cfset yearList = listremoveduplicates(valuelist(getData.year))> <table border="1"> <tr> <th>Year</th> <cfloop list="#materiallist#" index="l"> <th><cfoutput>#l#</cfoutput></th> </cfloop> </tr> <cfloop list="#yearList#" index="y"> <tr> <td><cfoutput>#y#</cfoutput></td> <cfquery dbtype="query" name="data" > select * from getdata where [year] = #y# </cfquery> <cfset thisMaterial = valuelist(data.material)> <cfset thisArea = valuelist(data.area)> <cfloop list="#materialList#" index="m" > <cfset item = listFindNocase( thisMaterial, m)> <td> <cfif item> <cfoutput>#listGetAt(thisArea, item)#</cfoutput> </cfif> </td> </cfloop> </tr> </cfloop> </table>
Thanks Dave, and sorry it took me so long to reply. While I was working through the problem, the content owner changed the way they want the information presented, so it ended up being a non-issue. But it looks like I can apply your tip to a similar problem on a different project, so thanks!