EasyCFM.COM ColdFusion Forums / ColdFusion Applications / Reference a Dynamic Column when outputting

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Reference a Dynamic Column when outputting

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

jaco5md
04-14-2011 @ 3:04 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 492
Joined: Jul 2005

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

-David

"Rock 'n' Roll 4 Ever"

Lyndon
04-14-2011 @ 4:04 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 66
Joined: Jun 2004

Select *  is, almost, never a good solution. It is hard to help and/or make sense of what you are trying to accomplish with out knowing the Table column names.
Show a few of the column names in table: vw_Variance and an example of the output you are trying to produce and maybe there will be enough information to help you.

Lyndon

jaco5md
04-14-2011 @ 4:08 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 492
Joined: Jul 2005

Thanks Lyndon,

I usually don't use Select *, but since two of these tablenames are going to be dynamic based on the array variables I thought I had to in order to successfully do the pivot.

Good news is I just figured out how to accomplish what I needed.  Will post solution soon.

-David

"Rock 'n' Roll 4 Ever"


Website Designed and Developed by Pablo Varando.