SQL: Select, dynamically created values as column

Question

I have to select data from a column then show as these values as another columns. But the struggle is, inside my column always new data will come and new cells will be created.

Product_Table:

ID   NAME
1    apple
2    orange

Selling_Table:

ID   PRODUCT_ID   DATE
1    1            2020-06-12
2    1            2020-05-03
3    2            2020-01-01
4    1            2020-07-23

What I Want

NAME   SELLING_DATE_1   SELLING_DATE_2   SELLING_DATE_3
APPLE  2020-06-12       2020-05-03       2020-07-23
ORANGE 2020-01-01       NULL             NULL

When there is a new date in selling table I want my SQL create another SELLING_DATE dynamically. As you notice when there is no SELLING_DATE data filled with null or we can replace basic text like 'not sold'

Answer 1

You can use window functions and conditional aggregation:

select
    name,
    max(case when rn = 1 then date end) selling_date_1,
    max(case when rn = 2 then date end) selling_date_2,
    max(case when rn = 3 then date end) selling_date_3
from (
    select p.*, s.date, row_number() over(partition by p.id order by s.date) rn
    from product_table p
    inner join selling_table s on s.product_id = p.id
) t
group by id, name

You can expand the query with more columns (that is, more conditional max()s) to handle more dates.