How to Transpose with and generate incremental ID
I have a data set as follows:
sprouts seller 3
pineapple seller 3
I want to End up as follows:
Seller Item 1 Item 2 Item 3 Item 4
Seller 1 apples - - -
Seller 2 oranges apples kiwi -
Seller 3 sprouts pineapple - -
I'm thinking I need to use the pivot operator but before that I need to generate an attribute containing the values (Item 1, Item 2, Item 3, Item 4 etc)
How would I do that? Using a regular expression in the form 'Item (X)' where X increments with each row up to a MAX value which is determined by counting the number of each distinct seller attribute?
Any help would be much appreciated!