Coldfusion set dynamic columns for query output

Question

I am trying to set dynamic query column headers to get their values from a query.

<cfoutput query="qryGetData">

        <cfloop from="-18" to="18" index="i">
        <cfif i GTE 0>
            <cfset variables["target_MonthPlus_#abs(i)#"] = "Testing" />

            <td>
                <cfoutput>#variables["target_MonthPlus_#abs(i)#"]#</cfoutput>
             </td>     
         <cfelse>
            <cfset variables["target_MonthMinus_#abs(i)#"] = "Testing" />



            <td>
            <cfoutput>#variables["target_MonthMinus_#abs(i)#"]#</cfoutput>
             </td>     

         </cfif>  

        </cfloop>

Code I have does not really work, I found it from another answer and I have tried all I can think of and tried using EVALUATE() even though I know I should not use.

So basically my query has 37 month fields starting from target_MonthMinus18 to target_MonthMinus1. And then target_MonthPlus0 to target_MonthPlus18. I have taken care of that plus and minus with the CFIF as you can see above. So only other thing different is that value of the month.

Closest things I have got to actually name the columns dynamically is something like this, but this just outputs the name of the column, which would return target_MonthPlus0, target_MonthPlus1, targetMonthPlus2, etc. But I need to use that name to return the actual value of the columns from the query.

 <cfif i GTE 0>
            <cfset monthInLoop = "target_MonthPlus_" & #ABS(i)#>

            <td>
                <cfoutput>#monthInLoop#</cfoutput>

target_monthMinus18 is a column name that may return a value of 100 from qryGetData that I need to display in its td

target_monthMinus17 is a column name that may return a value of 95 from qryGetData that I need to display in its td

target_monthPlus17 is a column name that may return a value of 205 from qryGetData that I need to display in its td

and so on...We are always going back 18 months in the past and 18 months in future as you can tell.

I have found several questions similar to this and have applied to my code but somehow they are trying to do different things or I still do not understand what I am doing wrong.

Thanks in advance for your help :)

PC

score 3 · Accepted Answer · answered Oct 18 '16 at 17:36

I think you may be over thinking this. You can access your columns directly from within the query using something like this:

<cfloop query="qryGetData">

      <cfloop from="-18" to="18" index="i">
        <cfif i GTE 0>
            <td>
                <cfoutput>#qryGetData["target_MonthPlus_" & abs(i)][currentrow]#</cfoutput>
             </td>     
         <cfelse>
              <td>
            <cfoutput>#qryGetData["target_MonthMinus_" & abs(i)][currentrow]#</cfoutput>
             </td>     

         </cfif> 
    </cfloop>
</cfloop>

This takes advantage of CF's query array syntax. Keep in mind you also have qryGetData.columnlist to work with (a list of all your columns). You might be able to work with that creatively as well.

I knew it. I was over thinking by trying so many wrong ways. Thanks for your help!! :) :) — ScrumMaster Guy, Oct 18 '16 at 17:58

Coldfusion set dynamic columns for query output

1 Answers1