Skip to main content
BreakawayPaul
BreakawayPaul
Inspiring
May 11, 2016
Question

Building a table from a cfquery

  • May 11, 2016
  • 2 replies
  • 1589 views

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.

This topic has been closed for replies.

2 replies

Dave Ferguson
Dave Ferguson
Participating Frequently
May 13, 2016

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
BreakawayPaulAuthor
Inspiring
August 30, 2016

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!

WolfShade
WolfShade
Legend
May 11, 2016

You mean like:

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

V/r,

^_^

BreakawayPaul
BreakawayPaulAuthor
Inspiring
May 11, 2016

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
WolfShade
Legend
May 11, 2016

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,

^_^

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices