We have the following table which contains data which scores a customer for a category. We want to transpose that into another table so it says which category the customer is most likely to be in.
CUSTOMERID|CATEGORY|SCORE
123 |MENS |25
123 |WOMENS |5
123 |KIDS |0
456 |MENS |0
456 |WOMENS |15
456 |KIDS |30
The data needs to tranposed into the following format so we know which category they are most likely to spend in. For example customer 123 has the highest score in the Mens category so there CAT1 = MENS.
CUSTOMERID|CAT1|CAT2 |CAT3
123 |MENS|WOMENS|KIDS
456 |KIDS|WOMENS|MENS
Is it possible to do this using SQL Server, I need to implement in SFMC so I am slightly limited and cant right a function.
Cheers for your help