Dynamic column name in cfquery

[Govindaram]

I have a scenario in which database column names are in a format COLUMN1, COLUMN2, ...COLUMNn. Number of column is not known while coding. But usually the column names are hard code while accessing the cfquery result like
cfqueryresult.COLUMN1.

I want to get the column names from 'cfqueryresult.ColumnList' and from that result i want to fetch the column values.
Is it possible to achieve this in coldfusion?

[Dan_Bracuk · Accepted solution]

<cfoutput query = yourquery>
<cfloop list = "#yourquery.columnlist#" index = "ThisColumn">
#yourquery[ThisColumn][currentrow]#
closing tags

[Newsgroup_User]

Dan Bracuk wrote:
> <cfoutput query = yourquery>
> <cfloop list = "#yourquery.columnlist#" index = "ThisColumn">
> #yourquery[ThisColumn][currentrow]#
> closing tags


And then start planing for the normalization of your database because
that is a classic de-normalized pattern that just leads to more and more
difficulties maintaining your application. Just like this one you have
just experienced!

[Dan_Bracuk]

quote:

---

Originally posted by: Newsgroup User


And then start planing for the normalization of your database because
that is a classic de-normalized pattern that just leads to more and more
difficulties maintaining your application. Just like this one you have
just experienced!

---

That wasn't my interpretation. I have some reports where the contents of a select clause depend on user input. It results in a similar scenario to what was described in the OP.

[Oci-One_Kanubi1]

quote:

---

Originally posted by: Dan Bracuk

quote:

---

Originally posted by: Newsgroup User

And then start planing for the normalization of your database because that is a classic de-normalized pattern that just leads to more and more difficulties maintaining your application. Just like this one you have just experienced!

---

That wasn't my interpretation. I have some reports where the contents of a select clause depend on user input. It results in a similar scenario to what was described in the OP.

---

Or maybe the OP was trying to write generic code that could be applied to arbitrary tables.

That was the situation which resulted in my question that you and Ian answered so helpfully: I use an Access template in which there are forms to facilitate entry of value lists (list-id, value, and label) and a metatable that describes each column of the actual working table , identifies any value-list that column references, and includes the prompt and (after processing) the HTML for that web page element. After I define the working table in the metatable I run a VB procedure that creates the working table and generates all the HTML in the metatable. Finally my generic ASP (and, coming soon, ColdFusion) references the metatable and the working table to generate an empty form, display a form populated from a DB row for editing, assign form values to a DB row, or delete a DB row. Same code, any table; all I have to do is define the table and the value-lists it needs.

This is a wonderful tool for me in a Social Sciences department where the Professors are always designing surveys to gather data. I can deplay a web-based survey in a few hours, and, with your help and many hours poring over manuals, I have the first example working in ColdFusion. And, incidentally, the ColdFusion version is way tidier, more compact, and more readable than the ASP version.

Dan, I'll be happy to answer other peoples' questions as soon as I am more confident in my knowlege of ColdFusion!

-Richard
--
Richard Hopley
Research Associate
Department of Social Science and Health Policy
Wake Forest University Medical School
Winston-Salem, NC