My source data has 3 columns:
This is an example of how my source data looks based on one id:
id | value | gid |
5555555 | Cookies | pastry |
5555555 | Daniel | chef |
I want to create 2 new fields based on this source data:
Here is the logic I want to use to create these fields:
- pastry type
- If [gid] = 'pastry' then [value]
- chef name
- If [gid] = 'chef' than [value]
This is how my output data is looking now:
id | value | gid | pastry type | chef name |
5555555 | Cookies | pastry | Cookies | |
5555555 | Daniel | chef | | Daniel |
My goal is to compress these two records into one record so it looks like this:
id | value | gid | pastry type | chef name |
5555555 | Cookies | pastry | Cookies | Daniel |
The only fields I need in the output data are these:
id | pastry type | chef name |
5555555 | Cookies | Daniel |
What's the best way to do this? I know I can join back to the source table multiple times to get this result but I am trying to avoid that.
Thank you
