Building a table from a cfquery
Copy link to clipboard
Copied
I'm trying to build a table of noise barrier area by material per year. I have a query that gives me this:
(I have years from 1963 - 2013, so the below is just a sample)
2013 Berm 30
2013 Block 172
2013 Cast-in-place Concrete 51
2013 Combination 16
2013 Concrete 0
2013 Glass 4
2013 Metal 63
2013 Not Specified 19
2013 Other 0
2013 Precast Concrete 278
2013 Wood 49
(the 3rd column is area X 10,000sqft)
the problem is, while the years are fine as rows, I need the materials to be column headings, and not in a single column. I swear I used to know how to do this, but now suddenly I'm drawing a blank.
Copy link to clipboard
Copied
You mean like:
Berm Block Cast-in-place Concrete Combination Concrete
2013 30 172 51 16 0
V/r,
^_^
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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> </td><cfloop index="idx" from="1" to="#val(tqmLen)#"><td>#thisQueryMeta.name[val(idx)]#</td></cfloop>
</tr>
<tr>
<cfoutput query="myQuery"><td>#myQuery.year[1]#</td><td>#myQuery.col1[1]#</td><td>#myQuery.col2[1]#</td><td>#myQuery.col3[1]#</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.)
HTH,
^_^
Copy link to clipboard
Copied
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[1]# gives me 0, as does any other number I plug into the [ ] (yrorigin is my year column)
But curiously, #myquery.material[1]# 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!
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
HTH,
--Dave
<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>
Copy link to clipboard
Copied
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!

