SQL get corresponding data where min or max value

Here is my data structure

I want to make a table in order to get my data in this format :

Here is what I have now :

select ID_group,
max(price) MaxPrice,
'' MaxPriceDate,
min(price) MinPrice,
'' MinPriceDate
from table
group by ID_group

>Solution :

We can use ROW_NUMBER here, along with pivoting logic:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID_group ORDER BY Price) rn1,
                ROW_NUMBER() OVER (PARTITION BY ID_group ORDER BY Price DESC) rn2
    FROM yourTable t
)

SELECT
    ID_group,
    MAX(CASE WHEN rn2 = 1 THEN Price END) AS MaxPrice,
    MAX(CASE WHEN rn2 = 1 THEN "Date" END) AS MaxPriceDate,
    MAX(CASE WHEN rn1 = 1 THEN Price END) AS MinPrice,
    MAX(CASE WHEN rn1 = 1 THEN "Date" END) AS MinPriceDate
FROM cte
GROUP BY ID_group
ORDER BY ID_group;