Exploding the group by cel in individual cases

I am trying to convert the following table (fictitious):

Person	year	price
Tom	2019	100
Klaas	2020	100
John	2019	120
John	2020	120
Klaas	2020	150
Klaas	2020	150
John	2020	150

into:

Person	year	price	price	price
Tom	2019	100	NaN	NaN
Klaas	2020	100	150	150
John	2019	120	NaN	NaN
John	2020	120	150	NaN

In fact, I group the data by person and year. Next, I desire to ‘explode’ the group by column. I work in azure data bricks resulting that both SQL and python works for me. I was thinking maybe I could add a extra cell to the row if Person==Person & year==year

Could someone help me? Btw. Does someone have the correct name for this problem.

Kind regards.

Answer

You can use row_number() and conditional aggregation:

select person, year,
       max(case when seqnum = 1 then price end) as price_1,
       max(case when seqnum = 2 then price end) as price_2,
       max(case when seqnum = 3 then price end) as price_3
from (select t.*,
             row_number() over (partition by person, year order by price) as seqnum
      from t
     ) t
group by person, year;