I wonder whether anyone might be able to help me with a bit of a problem that I am having. I'm trying to achieve something, but to be honest, don't know if it is even possible within FM ExecuteSQL, based on whether to commands are actually there.
For a basic Run down, I have three tables, Customer » Connections » Connection Variables. Customers can have multiple Connections and each Connection can have multiple variables, creating a One to Many » One to Many relationships.
The first task, is to export each connection based on a set of criteria, active Connections per customer. This is simple enough with a nice simple ExecuteSQL select. I have no problem with this.
The Second task is to identify the Variables related to a specific connection, with parameters, this again is nice and simple to get a list of variables; that comes out like this:-
The tricky bit, that I am trying to do, is have this returned a single table, without returning a row for each Connection_Var, but still returning a Row for each Connection. So the Output would look like this;
|A||1||ItemABC, ItemDEF, ItemGHI|
Is this anything that anyone has attempted, and found a solution for, staying with ExecuteSQL? I have a couple of ways that I can resolve the problem by creating a field in the Connection table that hold the related foundset of Variables ready to be called upon by the SQL Select, but I would like to keep this tidily with the Select if possible.
I have found a T-SQL that is designed to complete the same task, but I can't seem to get this to replicate into FM. http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string