Join a table without increasing lines, but columns instead

[#1]

Hello,

I have these 2 tables and would like to obtain the result table.

Table1:
ID	Name
1	A
2	B
3	C

Table2:
ID	Company	Field1
1	1000	X
1	2000	Y
2	1000	X
2	2000	Z
3	2000	Y

Result:
ID	Name	Comp.1000	Comp.2000
1	A	X	Y
2	B	X	Z
3	C	Y

I hope anyone can help. Thanks

[Gysbert_Wassenaar · Accepted solution]

Try:

Result:

load ID, Name

from ...table1source...;

join

Load ID, Field1 as Comp1000

from ...table2source...

where Company = 1000;

join

Load ID, Field1 as Comp2000

from ...table2source...

where Company = 2000;

[#3]

try to use generic load

*************************************

try this

Table1:

load * Inline [

ID,    Name

1,    A

2,    B

3,    C

];

join

Table2:

load * Inline [

ID,    Company,    Field1

1,    1000,    X

1,    2000,    Y

2,    1000,    X

2,    2000,    Z

3,    2000,    Y

];

Generic LOAD ID,'Comp.' & Company ,Field1 Resident Table1;

Generic LOAD ID,'Comp.' &Company ,Field1 Resident Table1;

[er_mohit]

see the attached file

output like this

ID	Name	Comp1000	Comp2000
1	A	X	Y
2	B	X	Z
3	C	-	Y