select - T-SQL - Pivot out Distinct N rows for each group

Question

I have a table similar to the one below with customers, products, and purchase date. I am trying to get a list of customers and their 3 most recently purchased DISTINCT products. I want to use purchase date as a means of ordering the results, but I don't want to see duplicate product IDs.

Customer	Product	PurchaseDate
1	a	2020-12-5
2	b	2020-12-5
1	a	2020-12-4
2	a	2020-12-3
1	b	2020-12-2
2	b	2020-12-1
1	c	2020-11-30
1	d	2020-11-29
2	b	2020-11-28

question from:https://stackoverflow.com/questions/65854805/t-sql-pivot-out-distinct-n-rows-for-each-group

Answer 1

Most recent distinct products is tricky. This requires one level of aggregation per customer and product and then another for pivoting:

select customer,
       max(case when seqnum = 1 then product end) as product_1,
       max(case when seqnum = 2 then product end) as product_2,
       max(case when seqnum = 3 then product end) as product_3
from (select customer, product, max(purchasedate) as max_purchasedate,
             row_number() over (partition by customer order by max(purchasedate) desc) as seqnum
      from t
      group by customer, product
     ) cp
group by customer;