What is the best approach for flattening a normalized data structure into single table

brianscott

Let's say my source data set has a characteristics table attached to my primary table; i.e., 

person == primary table with person identifier, name.
person_phone == one to many relationship based on person id with columns like 'phone_type' and 'phone', so if the system understood I had a home and a cell phone, I'd have two rows in this table. 

Now, let's further say that my destination table just wants to have a structure like this:
personId
cellPhone
homePhone

Running a blanket query against my characterstic table and joining created too many rows if I had more than one row in person_phone.  
I've been able to craft something that touches the same source one-to-many table (i.e., phone) and incorporates in formulas along the path to get there, but it seems clunky as anything and I really only cared about cellular and phone.  It occurs to me that if I had a lot of different values to access via descriptor I'd wind up querying the same table N times.

I'm thinking that I'm missing something pretty easy here.  

Any insight is appreciated. 

Thanks.
brian

kane_glendenning

Hi Brian,

I'm not 100% on whether I understand this correctly and I apologise if I'm over-simplifying but it sounds like you have the following structure:

• Fields in <Person> table: "Identifier", "Name"
• Fields in <Person_Phone> table: "Identifier", "Phone_Type", "Phone_Number"  
  • For this example lets say "Phone_Type" is categorical with the options being "Home", "Work", "Mobile"

As Such: 

And what you want is:

• Fields in Destination table: "Idenitfier", "Home_Phone",  "Work_Phone", "Mobile_Phone"

As Such: 

This can be obtained through the Crosstab tool with Name set as the Grouping field, "Phone_Type" as the Header Field and "Phone_Number" as the Data Field. You will need to tick a Methodology but for this instance either Sum or Average will be fine as long as you check your data.

If there is a possibility of having multiple entries for the same Person/Type combination (i.e. 2 Mobile numbers for one person, represented by 2 separate lines) then first perform a sort based on "Identifier", "Phone_Type", then a Multi-Row Formula Tool with the following updating Phone_Type (Note: This will only work for 2 instances of the same combination, it would need to be edited further, probably dynamically, if there was for instance 3 Mobile Numbers for one person):

IF [Identifier]==[Row-1:Identifier] && [Phone_type]==[Row-1:Phone_type]
THEN [Phone_type] + " 2"
ELSE [Phone_type]
ENDIF

Let me know if this does not answer your questionI can update this further if it does not answer your question.

Thanks,
Kane