How to do one to many attributes field mapping at the same time?

General

How to do one to many attributes field mapping at the same time?

Hi

Many thanks for any help in advance.

 I am new in teradata. Here is my case:

Test Table:  

entity_id      cust_id1        cust_id2        cust_id3          cust_id4

Table Cust:

 cust_id          fname            lname            email             phone

query I used:

SELECT b.entity_id,  b.original_bml_id,

CASE WHEN a.cust_id = b.cust_id1 THEN   cust_id1 END AS  cust_id1 ,

CASE WHEN a.cust_id = b.cust_id1 THEN fname  END AS fname1,

CASE WHEN a.cust_id = b.cust_id1 THEN lname END AS  lname1,

CASE WHEN a.cust_id = b.cust_id1 THEN  email END AS email1,

CASE WHEN a.cust_id = b.cust_id2 THEN  cust_id2 END AS  cust_id2 ,

CASE WHEN a.cust_id = b.cust_id2 THEN fname END AS fname2,

CASE WHEN a.cust_id = b.cust_id2 THEN lname END AS lname2,

CASE WHEN a.cust_id = b.cust_id2 THEN  email END AS email2,

...

...

FROM cust a, test b

WHERE a.cust_id = b.cust_id1

OR a.cust_id = b.cust_id2

OR a.cust_id = b.cust_id3

OR a.cust_id = b.cust_id4;

cust_id1, cust_id2, cust_id3, and cust_id4 have  the one-to-one match value in cust table. With the query I want to create a new table with below columns:

entity_id     cust_id1    fname    lname    email     phone     cust_id2    fname    lname    email     phone     cust_id3 ... cust_id4 ...

My query only match one cust_id in the test table, and ignore the rest. What should I do to gain the result?

Thank you so much!

Tags (1)