You can use "if" statements in the script. So nearly what you wrote there and you just need to nest them, eg:
If( T1_Value = 'Type', [Trans2 Types field],
If( T1_Value = 'Calendar', [Trans2 Calendar field])
Is this the sort of thing you are after? I am assuming you joined your tables together, if not, let me know.
If the field does not exist in the DB, just add it as text and "call" it the same name as the field you need. Just the same as when you rename a field, use the "as" to rename it. EG when loading table 2:
Field1 as [Field Name],
'Home' as Address,
From ...Table 2...;
This would Load all the information in field 1 but call it [Field Name], and will create a field adjacent to the table 2 data which just contains the word 'Home'