Call a custom query through a transformation ?

Instead use custom query in the transformation, why don't you use a repeater with custom query web part, which you can get data first with the query and maybe the where condition based on the form then apply transformation? (be sure to sanitize the user input before pass it to SQL call)

That make sens but how do I access data inside the transformation ? I don't want to list the data, only for processing.

Sure, assuming you are using ASCX transformation and doing it over custom table where one of columns contains a query that you need to run. You can do in your transformation smth like this:

<script runat="server">
  string QueryName = "";

  protected override void OnInit(EventArgs e) 
  {   
    // Your Regular C# code
    QueryName = Eval("ColumnWithQueryName"); 
    var query = new CMS.DataEngine.DataQuery(QueryName);
    DataSet ds = query.Result; // your result set - the query will be run on each iteration
    .....       
  }
</script>
<%#QueryName%>

But from what I understand you need do it more likely you need an AJAX call to a web service based on a user input. Because repeater/transformation run on the server, when a user filling out a form the html (i.e. the output of your repeater with transformation) is already rendered to the client side.

Is the data you are trying to display from the custom table part of the form or post back?

For example, a example I can think of is: There is a drop down, based on what's been selected in that drop down, I need to use custom query to select a list of values from a custom table and generate a second drop or check box options.

Is that similar to what you are trying to do or you are looking for something like. Based on user's choice from a drop down, on the post back, display a list of data from a custom table.

Definitely on the post back. I will try Peter's solution but I am not quite sure what is a ColumnWithQueryName. I thought a query was link to a custom table not one of its columns...

If it's one post back, how do you plan to get the "value" for what the visitor selected in the form?

I would pass it through the query string of the redirect url.

In that case, use a Repeater with custom query web part on the page. And select your custom query like this one:

SELECT Fields FROM YYYYY WHERE ##WHERE##

Then in the property of the repeater WHERE add: FieldName = '{%QueryString.FieldName|(handlesqlinjection)true|(identity)GlobalAdministrator%}'

This repeater will get the data you need. Then you only need to transform the data as normal repeater. The above query also takes care of SQL injection problem. (https://devnet.kentico.com/articles/security-avoiding-sql-injection-(sqli))

Thank you !