Reference a Column in a Dynamic Query

[GWG]

I'm creating a simulated a dynamic crosstab query from my SQL 2K table by
looping over a recordset and creating my SQL statement. Everthing works fine
until I try to output the query. Here is a bit of code.

<cfquery name="list" datasource="#request.App.dsn#">
SELECT distinct swa, legalValue
FROM ActiveCL INNER JOIN CoreLV ON ActiveCL.swa = CoreLV.lKey
WHERE ActiveCL.facility=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#attributes.facility#">
</cfquery>

<cfset sqlString="">

<cfoutput query="list">
<cfset sqlString="#sqlString# sum(case b.swa when #list.swa# then 1 else 0
end) as '#list.swa#',">
</cfoutput>

<cfset sqlString="#sqlString# 'placeHolder' as placeHolder">

<cfquery name="Audit" datasource="#request.App.dsn#">
SELECT distinct CLItems.clKey, min(CLItems.clDescription) as tclDescription,
#preserveSingleQuotes(sqlString)#
FROM CLItems LEFT JOIN
(select Samples.clKey, Samples.swa
from Samples
where Samples.sampleTime > #createODBCDate(audit.lastRefresh)# and
Samples.facility = #attributes.facility#) b
ON CLItems.clKey = b.clKey
WHERE CLItems.clCategory=#C_SAFETYAUDIT# AND CLItems.isActive=#C_YES#
GROUP BY CLItems.clKey
ORDER BY CLItems.clKey
</cfquery>

This creates a query with dynamic column names. I need to loop over this
query "Audit" and output the results. I need to reference the dymanic column
names in my output but cannot seem to get the syntax write.

Here is what I have tried - and does not work...

<cfoutput query="safetyAudit">
<tr>
<td>#safetyAudit.tclDescription#</td>
<cfloop query=list>
<cfset outField="safetyAudit.#List.swa#">
<td>#evaluate(outField)#</td>
</cfloop>
</tr>
</cfoutput>

Any ideas?

Thanks...

[JMGibson3]

I've always been partial to lists in these situations. Nested loops can stall
on the first outer occurence within the inner loop, although I don't know that
that is what is happening to you. Try:

<cfset colList = safetyAudit.ColumnList>
<cfoutput query="safetyAudit">
<tr>
<td>#safetyAudit.tclDescription#</td>
<cfloop index="ColName" list="#colList#">
<td>#Evaluate("safetyAudit." & colName)#</td>
</tr>
</cfoutput>

[GWG]

Worked great! Thanks