Scenario is as follows:
Old Access DB schema
COL_NAME DATA_TYPE
'HAIR' Yes/No
'FACE' Yes/No
'LIP' Yes/No
'ORAL' Yes/No
'ID' (PK) Text
New database design schema:
COL_NAME DATA_TYPE
'ID' VarChar (PK)
'PRODUCT_TYPE' VarChar (PK)
Question:
How can I check the 'Yes' columns from the old schema and insert each as a separate record into the new table?
Transform multiple columns into multiple records
Moderators: chulett, rschirm, roy
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
A simple way is to have one input and four outputs. Your question wasn't completely clear but here is a guess. You want to turn the following:
HAIR FACE LIP ORAL ID
YES NO NO YES PLEASE 12343
YES YES YES NO 435
Into this:
12343 HAIR
12343 ORAL
435 HAIR
435 FACE
435 LIP
You do this by having four outputs from your transformer, one for each product type. You then add a constraint for each output which checks the yes/no value of a particular product type, for example "InputColumn.HAIR = 'Yes'" on the first one and "InputColumn.FACE = 'Yes'" on the second one. This gives you from 0 to 4 rows outputted per input row depending on how many Yes values are found. Each output inserts into the destination table.
Vincent McBurney
Data Integration Services
www.intramatix.com
HAIR FACE LIP ORAL ID
YES NO NO YES PLEASE 12343
YES YES YES NO 435
Into this:
12343 HAIR
12343 ORAL
435 HAIR
435 FACE
435 LIP
You do this by having four outputs from your transformer, one for each product type. You then add a constraint for each output which checks the yes/no value of a particular product type, for example "InputColumn.HAIR = 'Yes'" on the first one and "InputColumn.FACE = 'Yes'" on the second one. This gives you from 0 to 4 rows outputted per input row depending on how many Yes values are found. Each output inserts into the destination table.
Vincent McBurney
Data Integration Services
www.intramatix.com