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

[ilssac]

See what <cfoutput>#getVariance.columnList#</cfoutput> gets you.

Message was edited by: ilssac And now that I read the list line of your post I see that you have.

[ilssac]

RockDadMD wrote:

but that didn't work, I'm not sure what else to try.  Thanks for your time and attention.

So HOW did it not work?  What results did you actually get and what results did you want to get.

[Dan_Bracuk]

What would happen if you made it all into one query?  Instead of this:

FOR ProgramID IN ([#program[2][1]#],[#program[1][1]#])) AS PivotTable

do this

FOR ProgramID IN (Select Top 2 ID FROM vaccine_program
WHERE Program like '%Seasonal Flu%'
Order By ID Desc
) AS PivotTable