Cfloop in sql statement?

Question

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.

try this `sum(case when quarter = '#i#' then 1 else 0 end) as year#i#`. You'll also need a comma after all but the last one to be valid SQL. Also I think you may just be able to do `select count(quarter) as total, quarter from testTable group by quarter` depending on what output you are expecting. — John Whish, Mar 17 '16 at 16:18
So what and how I should handle comma on the end? If I have only one year I will not need comma, if I have 2 and more I need then? — espresso_coffee, Mar 17 '16 at 16:24
I don't know that this query should be done in code. I agree with both John and James: this can essentially be reached much easier through the database itself. Plus the above query will select _ALL_ rows in the database and won't limit them to the startyear and endyear. And "quarter = 2015" doesn't really have much meaning. I'm not sure what the intent is here. Can you use SQLFiddle or something of the like to give us a basic database schema and what you're expecting the query to return? — Shawn, Mar 18 '16 at 18:56

score 3 · Accepted Answer · answered Mar 17 '16 at 17:21

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.

I don't think this will return more than 1 on any year. It's running an aggregate on 1 or 0 without any sort of grouping, so the sum() isn't really doing anything. And I don't think the queryparam is really needed here, since the loop is the one generating i. — Shawn, Mar 18 '16 at 19:24

Cfloop in sql statement?

1 Answers1