0

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

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
  • 1
    Thanks - this worked first time! Excellent answer and it runs pretty quick as well! – BDOB83 Oct 23 '15 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.