Two part problem - rows to string and substitute string in query

Question

Hi, Using Alteryx, I'd like to take a column from a query transform it into a list and then use that list as part of a second query. As an example, col1 has rows 1,2,3 I can transform it into a list 1,2,3 using the summarize but I need it to be '1','2','3'. I believe I can use a regex to add the apostrophes to the string (I thought I could configure the summarize tool but maybe not). Next can I take the above string '1','2','3' and replace it in part of a query such as 'select * from table where f1 in ();' where the above string can be substituted for . I think I'm close by using dynamic input but having some struggles so thought I might reach out to the experts. Thanks, Jeff

jdunkerley79 · Accepted Answer

In the summarise tool when doing the concatenate you can specify that the string should start with a ' and use ',' to join and finish with another ':

If you wish to do by REGEX then:

Expression:

\b(\d+)\b

Replacement:

'$1'

should work.

In terms of the dynamic input, I would suggest a query like:

select * from table where f1 in ('XXX')

and then as you describe replace a specific string (the 'XXX' part):

One thing to check from what you show is that the f1 field is a string field as if not then you need no single quotes. Also if is string field make sure no entries in list with a single quote as that will break it.

Sample of first part is attached

.