Building a table from a cfquery

[BreakawayPaul]

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.

[WolfShade]

You mean like:

              Berm          Block          Cast-in-place Concrete          Combination          Concrete
2013            30            172                              51                   16                 0

V/r,

^_^

[BreakawayPaul]

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.

[WolfShade]

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[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,

^_^

[BreakawayPaul]

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!

[BreakawayPaul]

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.

[Dave Ferguson]

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>

[BreakawayPaul]

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!