Search code examples
sql-servercoldfusionconcatenationcfloop

Cfloop in sql statement?

I was wondering if it's possible to loop inside sql statement. In my case I had two years that I created for testing purpose. Now I would like to create years dynamically because I can have more than two records. Here is my code that I used for testing purpose:

Select sum(case when quarter = '2015' then 1 else 0 end) as year2015,
       sum(case when quarter = '2016' then 1 else 0 end) as year2016
From testTable

Now I have cfloop that gives me years as 2015, 2016, 2017, ... So I tried something like this but that did not work:

Select 
      <cfloop from="#startYear#" to="#endYear#" step="1" index="i">
            sum(case when quarter = i then 1 else 0 end) as CONCAT('year',i)
      </cfloop>
From testTable

Error message:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'year'.

I'm not sure if this is the best way to do this, if anyone can help please let me know.

Solution

  • Just remember that your resulting SQL string has to be valid. CF variables need to be passed in in your case statement. 

    <cfquery name="foo" datasource="xxx">
    SELECT  1 AS placeholder
            <cfloop from="#startyear#" to="#endyear#" index="i">
                -- prepend comma before each statement. Also, variable i must be passed in since it exists as a CF variable, not a SQL variable.
                ,SUM(CASE WHEN quarter = <cfqueryparam value="#i#" cfsqltype="cf_sql_integer" />  THEN 1 ELSE 0 END) AS year#i#
            </cfloop>
    FROM    testTable
</cfquery>

    NOTE: this assumes the datatype of quarter is an integer. Change cfsqltype attribute as needed.

    You could also use a PIVOT function to build out your columns.