If multiple Rows, combine into one

[arnettmh]

I have data that looks similar to the following:

ID	Region	Name	Order Count	Type
1003	North	Bob Jones	5	Primary
1004	West	Jim Cobbler	7	Primary
1004	East	Jim Cobbler	3	Secondary
1005	North	Patricia Smith	8	Primary
1005	Central	Patricia Smith	2	Secondary

I want to have a single row by ID where we would have a Primary Region with a total order count for the Primary Region and then a Secondary Region with a total order count for the Secondary Region.  At most, there are only ever 2 rows by ID.  So, there will always be either 1 or 2 rows per ID.

Want to end up with something like this:

ID	Name	Primary Region	Primary Region Order Count	Secondary Region	Secondary Region Order Count
1003	Bob Jones	North	5	Null	Null
1004	Jim Cobbler	West	7	East	3
1005	Patricia Smith	North	8	Central	2

[binuacs · Accepted solution]

@arnettmh One way of doing this with the Join tools

[arnettmh]

Thanks @binuacs , this worked well for me.  I was trying to use the Transpose tool and Cross Tab tool, but this was a simpler method.

[BS_THE_ANALYST]

@arnettmh I added some extra data to try and show you how to future proof your work if you come across this scenario again.

You just need to keep adding another layer by adding another Filter tool iteratively. 

Cool tip, to help with the renames, inside the options of join tools you can clear all renames, and then highlight the ones you want to add the prefix to i.e. Primary, Secondary, etc. Makes it so much easier for renaming.