CFLOOP on query and output

[emerickson]

I have a SQL table with columns QD1-QD8. I have a query that looks like this:
<CFQUERY name=&quot;ShowData&quot; datasource=&quot;EdropSQL&quot;>
SELECT
SUM(CASE WHEN QD1 = '5' THEN 1 ELSE 0 END) AS StA1,
SUM(CASE WHEN QD1 = '4' THEN 1 ELSE 0 END) AS A1,
SUM(CASE WHEN QD1 = '3' THEN 1 ELSE 0 END) AS SA1,
SUM(CASE WHEN QD1 = '2' THEN 1 ELSE 0 END) AS D1,
SUM(CASE WHEN QD1 = '1' THEN 1 ELSE 0 END) AS SD1,
SUM(CASE WHEN QD1 = '0' THEN 1 ELSE 0 END) AS NA1,

Trainer_UGD, CourseID, CommentsD1
FROM DeliveryEval
</Query>
Then some more queries based on this one, and then the output, which is a table showing how many of each category (StA1, A1, SA1, etc) there are. I could have a query like this one for each QD, but I would rather save myself the typing. Is there any way I could loop this query so that QDx will be increased each time and StAx, Ax, SAx, etc also? And then, will it work with a Loop on the output to show 1 table for each QD?
Thanks,
Eva Erickson

 

[csteinhilber]

If I understand what you're asking... the SQL within a CFQUERY tag is just text... so you can affect it as if you were in a CFOUTPUT tag:

<CFQUERY name=&quot;ShowData&quot; datasource=&quot;EdropSQL&quot;>
SELECT   
    <CFLOOP from=&quot;1&quot; to=&quot;6&quot; index=&quot;whichQD&quot;>  
    SUM(CASE WHEN QD#whichQD# = '5' THEN 1 ELSE 0 END) AS StA#whichQD#, 
    SUM(CASE WHEN QD#whichQD# = '4' THEN 1 ELSE 0 END) AS A#whichQD#, 
    SUM(CASE WHEN QD#whichQD# = '3' THEN 1 ELSE 0 END) AS SA#whichQD#,
    SUM(CASE WHEN QD#whichQD# = '2' THEN 1 ELSE 0 END) AS D#whichQD#, 
    SUM(CASE WHEN QD#whichQD# = '1' THEN 1 ELSE 0 END) AS SD#whichQD#,
    SUM(CASE WHEN QD#whichQD# = '0' THEN 1 ELSE 0 END) AS NA#whichQD#,
    </CFLOOP>
    Trainer_UGD, CourseID, CommentsD1
    FROM DeliveryEval
</CFQUERY>

or, even more dynamic:

<CFSET lstColumnList = &quot;NA,SD,D,SA,A,StA&quot;>
<CFQUERY name=&quot;ShowData&quot; datasource=&quot;EdropSQL&quot;>
SELECT   
    <CFLOOP from=&quot;1&quot; to=&quot;6&quot; index=&quot;whichQD&quot;>  
      <CFLOOP from=&quot;#ListLen(lstColumnList)#&quot; to=&quot;1&quot; step=&quot;-1&quot; index=&quot;whichColumn&quot;>
         <CFSET sColumnName = ListGetAt(&quot;#lstColumnList#&quot;,whichColumn)>
         <CFSET nAdjustedValue = whichColumn - 1>
         SUM(CASE WHEN QD#whichQD# = '#nAdjustedValue#' THEN 1 ELSE 0 END) AS #sColumnName##whichQD#, 
       </CFLOOP>
    </CFLOOP>
    Trainer_UGD, CourseID, CommentsD1
    FROM DeliveryEval
</CFQUERY>

but that's probably a little risky (it requires that each column name be in the proper position in the list).



-Carl

 

[r937]

don't forget the appropriate GROUP BY clause!


rudy

http://r937.com/