Same ID in two columns in one table

[sculptorlv]

Hello!

Hope for your help.

I have one table, in wich ID of companies are shown in TWO columns. Like:

Table 1.

Red ID  Black ID

F1          F1

F4          F5

F6          F8

And I have another table, where these ID are stored with its names. Like:

Table 2.

ID          Name

F1          name 1

F2          name 2

F3          name 3

F4          name 4

F5          name 5

F6          name 6

F7          name 7

F8          name 8

How can I in a SELECT form get such info:

Result table:

Red ID     Red Name     Black Name

F1              name 1          Name 1

F4               name 4          name 5

F6               name 6          name 8

Thank you in advance!

[manojkulkarni]

Create Apply Map Table using Table 2 data, then update Table 1 data with Name values

MapTab:

Mapping

Load

ID          Name

F1          name 1

F2          name 2

F3          name 3

F4          name 4

F5          name 5

F6          name 6

F7          name 7

F8          name 8

Load

Red ID,
ApplyMap('MapTab:',Red ID) AS RedName,

ApplyMap('MapTab:',Black ID) AS BlackName;

Table1

[sculptorlv]

Can I do this mapping table withoth defiing all values? I have thousands of them.

[Kushal_Chawda]

Below is the another approch

Table1:

Load [Red ID]

        [Black ID]

From table1;

left join(Table1)

Load ID as [Red ID]

        Name as [Red Name]

From table2;

left join(Table1)

Load ID as [Black ID]

        Name as [Black Name]

From table2;

[its_anandrjs]

Yes you can try mapping load here

Ex:-

Data:

Mapping

LOAD * INLINE [

    ID, Name

    F1, name 1

    F2, name 2

    F3, name 3

    F4, name 4

    F5, name 5

    F6, name 6

    F7, name 7

    F8, name 8

];

Id:

LOAD *,ApplyMap('Data',[Red ID]) as [Red Name],

            ApplyMap('Data',[Black ID]) as [Black Name];

LOAD * INLINE [

    Red ID, Black ID

    F1, F1

    F4, F5

    F6, F8

];

Note:- You can use thousands of records in this case.

Regards,

Anand

[manojkulkarni]

You can, I have just given example for it.

You can use your data source as input for this table