SQL IN statement

I can see a few obvious problems with the SQL:

1) You should not use quotes surrounding #variables.test#. The resulting SQL should be:

WHERE myCol IN (myList)

and not
WHERE 'myCol' IN (myList)

2) You don't have txCommaDelimitedList surrounded by pound signs, so CF is treating it as the literal text: "txCommaDelimitedList". Look into using <cfqueryparam> you can use the list="Yes" parameter to handle comma delimited lists (even integers).

WHERE #Variables["test"]# IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#txCommaDelimitedList#" list="Yes">)

3) Using a CF variable like that in your query statement is incredibly insecure. You're pretty much setting yourself up for a SQL injection attack. You might look into using a switch/case statement, or at least performing an initial query to make sure that Variables.test is a valid column name and not something like:

1=1;
DROP TABLE
SELECT * from SomeTable WHERE 1

bc txCommaDelimitedList is a varchar field since it has the commas in sql

Why is variables.test in quotes?

Whats the error you get?

You'd need to loop over variables.test and create dynamic statement.

<cfset tempSQLStatement ="WHERE #listFirst(variables.test)# IN (txCommaDelimitedList)" />

<cfloop list="#listRest(variables.test)#" index="i">
<!--- you could also use OR --->
<cfset tempSQLStatement = "AND #i# IN (txCommaDelimitedList)" />

</cfloop>

<cfquery name="test" datasource = "ds1">
SELECT *
FROM tblTest
#tempSQLStatement#
</cfquery>

Normalize your database and you won't have problems like this.