Loop through SQL table and build SQL string using value of each record

[user_km]

I have reports in database tables that are separated by group name. The group names exist in a separate table on a different database. They all have the same fields but with different table names like Report_Group_1, Report_Group_2, etc. The group names exist in a separate table and I want to combine them together. I want to build a SQL statement that uses union all to combine the Report tables so it would be something like

SELECT * FROM Report_Group_1 UNION ALL SELECT * From Report_Group_2 

etc..

I figure that the most efficient way to do this would be to loop through each record in the [Group] table to create a string variable that is updated as it loops through each value in the [Group] table. After it iterates through every record, the string variable is executed as a query on the database. How would I do this in Alteryx? I think I would use an iterative macro to loop through each record in he [Group] table but I'm not sure how to go about building a string variable that updates as each record is iterated through.

[MarqueeCrew · Accepted solution]

I wonder if you could read table 1 and bring the results back and then use the summarize to concatenate the table list.

Start: Select * from
Separator: blank
End: Union All (space)



Concatenate (Strings Only): Takes all the records in a group and concatenates the strings.

Concatenate Properties: You can insert any character or string in the boxes listed below, or leave them blank. Supported escape characters include: \n (new line), \t (tab), \r (carriage return), \s (white space character)
Start: The character specified will appear at the beginning of the concatenated string. It is left blank by default.
Separator: The character specified will appear in between each value of the concatenated string. The default is a comma (,).
End: The character specified will appear at the end of the concatenated string. It is left blank by default.

Then cleanup the results for proper syntax.

[user_km]

@MarqueeCrew

Your suggestion basically worked right away without the need for any cleanup. The only thing I adjusted was instead of Union All (space) at the End, I used UNION ALL SELECT * FROM (space).

I think this solution was much more efficient than using a macro to loop through each record in the list to build the string.

Thanks!