Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Building a table from a cfquery

Contributor ,
May 11, 2016 May 11, 2016

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.

1.4K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 11, 2016 May 11, 2016

You mean like:

              Berm          Block          Cast-in-place Concrete          Combination          Concrete

2013            30            172                              51                   16                 0

V/r,

^_^

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 11, 2016 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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 11, 2016 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,

^_^

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 11, 2016 May 11, 2016

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!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 11, 2016 May 11, 2016

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 13, 2016 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>

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 30, 2016 Aug 30, 2016
LATEST

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!

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