How to turn a specific field's records into field names?

[StephenBurns]

Hi all,

I have the following dataset that I would like to transform but do not know how. I would like to take the records in the 'Purchase / Sale' field and turn them into field headers and then have the value associated with the record be placed under the new field header. Before and after shown below. I feel as though I can get the desired result by doing some extractions and merges but was wondering if there was a simpler way. Please let me know if anyone has any suggestions. Thanks in advance!

Original

Code	Purchase / Sale	Value
A	Purchase	5
B	Purchase	6
B	Sale	-3
C	Sale	-5
D	Purchase	9

Adjusted

Code	Purchase	Sale
A	5	0
B	6	-3
C	0	-5
D	9	0

[jdunkerley79 · Accepted solution]

You can use a Cross Tab tool to do this.

Group by code, using the Purchase / Sale as Column Header and the Sum of the Value for the Values.

This will leave nulls in the table but this can be fixed using a Data Cleansing tool