I need to create 3 different sub-columns from single column.
This is my sample data
| Company code | Attributed fields |
| AA | Manager |
| AA | CIO |
| AA | IT Lead |
| AA | Dev Lead |
| AA | IT Staff |
| AA | Dev Staff |
| AB | Manager |
| AB | CIO |
| AB | IT Lead |
| AB | Dev Lead |
| AB | IT Staff |
| AB | Dev Staff |
I need output something similar to this.
| Company | Level 1 | Level 2 | Level 3 |
| AA | Manager | IT Lead | IT Staff |
| AA | CIO | Dev Lead | Dev Staff |
| AB | Manager | IT Lead | IT Staff |
| AB | CIO | Dev Lead | Dev Staff |
Level 1:
Company Name,
Attributed fields as Level1
If Attributed fields = Manager, '1' if Attributed fields = CIO, '2', as Key1
Resident
Sample Table
Level 2:
Company Name,
Attributed fields as Level2,
If Attributed fields = IT Lead , '1', If Attributed fields = Dev Lead , '2' as Key2
Resident
Sample Table
Level 1 and Level 2 Join:
Load
Company Name & Key1 as Key3
Level1
Resident
Level 1
Inner join
Company Name & Key2 as Key3,
Level2,
Resident
Level 2
I got output something like this.
| Company | Level 1 | Level 2 |
| AA | Manager | IT Lead |
| AA | CIO | Dev Lead |
| AB | Manager | IT Lead |
| AB | CIO | Dev Lead |
But I'm not sure how to proceed further
