AnsweredAssumed Answered

ExecuteSQL - One to Many Concatenation

Question asked by Padster on Aug 8, 2013
Latest reply on Aug 10, 2013 by Padster

Hi All,

 

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.

 

CustomerIDConnection_ID
A1
A2
B3
B4
C5
D6

 

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:-

 

Connection_IDConnection_Var
1ItemABC
1ItemDEF
1ItemGHI
2ItemJKL
2ItemMNO
3ItemPQR
4ItemSTU
4ItemVWX
5ItemYZA
6ItemBCD

 

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;

 

CustomerIDConnection_IDConnection_Var's
A1ItemABC, ItemDEF, ItemGHI
A2ItemJKL, ItemMNO
B3ItemPQR
B4ItemSTU, ItemVWX
C5ItemYZA
D6ItemBCD

 

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

 

Regards,

 

Pad

Outcomes