SFMC Query help - transpose data in SQL Server

Question

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

Answer 1

I've used this method in SFMC:

select 
    cs.customerid
    , max(case when cs.ranking = 1 then category end) cat1
    , max(case when cs.ranking = 2 then category end) cat2
    , max(case when cs.ranking = 3 then category end) cat3
FROM (
 select 
    cs0.customerid
    , cs0.category
    , cs0.score
    , row_number() over(partition by cs0.customerid order by cs0.score desc) ranking
  from CustomerScore cs0
) cs
group by
    cs.customerid

Output

customerid|cat1|cat2  |cat3
123       |MENS|WOMENS|KIDS
456       |KIDS|WOMENS|MENS