Highlighted

Building a table from a cfquery

Contributor ,
May 11, 2016

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.

Views

870

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Building a table from a cfquery

Contributor ,
May 11, 2016

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.

Views

871

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 11, 2016 0
LEGEND ,
May 11, 2016

Copy link to clipboard

Copied

You mean like:

              Berm          Block          Cast-in-place Concrete          Combination          Concrete

2013            30            172                              51                   16                 0

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 11, 2016 0
Contributor ,
May 11, 2016

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 11, 2016 0
LEGEND ,
May 11, 2016

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

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 11, 2016 0
Contributor ,
May 11, 2016

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 11, 2016 0
Contributor ,
May 11, 2016

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 11, 2016 0
Engaged ,
May 13, 2016

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 13, 2016 1
Contributor ,
Aug 30, 2016

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 30, 2016 0