3 Replies Latest reply on Apr 14, 2011 9:20 AM by Dan Bracuk

    Referencing a Dynamic Variable while outputting

    MusicManMD

      I have a somewhat unique issue I'm not sure how to deal with.  I am using a pivot table within my query to get data ready to display, however, two of the column names get generated dynamically.  How can I reference them when I output the data from teh query without knowing the column names first?  I hope I'm not being realy stupid but I can't seem to figure this out and was hoping you could point me in the right direction.

       

      I'm doing this so I don't have to keep changing the report from year-to-year, making it more dynamic.

       

      Here is what I am using:

       

      <cfquery name="getP" datasource="#request.DSN#">
      Select Top 2 ID, Left(Program,4) AS ProgYear
      FROM vaccine_program
      WHERE Program like '%Seasonal Flu%'
      Order By ID Desc
      </cfquery>

       

      <cfset program = ArrayNew(2)>

      <cfloop query="getP">
      <cfset program[currentrow][1] = #getP.ID#>
      <cfset program[currentrow][2] = #getP.ProgYear#>
      </cfloop>

       

      Using "Select *" so I can pivot the data.

      <cfquery name="getVariance" datasource="#request.DSN#">
      SELECT *
      FROM    (SELECT * FROM vw_Variance) AS DataTable
      PIVOT
      (SUM (RequestTotal) FOR ProgramID IN ([#program[2][1]#],[#program[1][1]#])) AS PivotTable
      ORDER BY uicID
      </cfquery>

       

      Currently [#program[2][1]#] = 3 and [#program[1][1]#] = 1 however next year the values may be 3 and 5.

      I hope this makes sense.

       

      I've tried using this:
      <cfset colList = getVariance.ColumnList>
      and concatenating the variable but that didn't work, I'm not sure what else to try.  Thanks for your time and attention.

       

      -David