Concatenate, or Join, or ?

[#1]

Cracking my head on the following...

I have two tables

Table1 has every row only one case.

Table2 can have multiple row from the same case (max. 3 times)

I want to merge both tables, but with ALL the collums, and additional colloms (form the other table, but with prefixes (max till 3) , like this:

Starting to get desired data using IF statements, but then the load starting to get VERY long... already with my test data, this wil lbe unworkable with the real data

Hope you understand my point, and even more hope that you have the solution (:

[Gysbert_Wassenaar · Accepted solution]

You can try using a generic load like below.

T1:
load * inline [
ID, Desc
1, Case1
2, Case2
];

T2:
CrossTable(Key,Value,2)
load RowNo() as RowId,* Inline [
ID, INFO1, INFO2, INFO3
1, text1,text2,text3
1,text11,text22,text33
];

T3:
Generic
load ID, Key & '_' & RowId as F, Value
Resident T2;

drop table T2;

[#3]

Hi Gysbert,

Looks like I am getting somewhere your code gives me the desired result...

Now translating to my own table structure (above was an example ofcourse)

Many many thanks!