7 Replies Latest reply on Aug 30, 2016 11:15 AM by BreakawayPaul

    Building a table from a cfquery

    BreakawayPaul Level 2

      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. Re: Building a table from a cfquery
          WolfShade Level 4

          You mean like:

           

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

           

          V/r,

           

          ^_^

          • 2. Re: Building a table from a cfquery
            BreakawayPaul Level 2

            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.

            • 3. Re: Building a table from a cfquery
              WolfShade Level 4

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

               

              ^_^

              • 4. Re: Building a table from a cfquery
                BreakawayPaul Level 2

                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!

                • 5. Re: Building a table from a cfquery
                  BreakawayPaul Level 2

                  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.

                  • 6. Re: Building a table from a cfquery
                    Dave Ferguson Level 3

                    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>
                    

                     

                    1 person found this helpful
                    • 7. Re: Building a table from a cfquery
                      BreakawayPaul Level 2

                      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!