Getting averages based on column values
SELECT color, 
       AVG(CASE WHEN dimension = 'length' THEN value END) avg_length, 
       AVG(CASE WHEN dimension = 'width' THEN value END) avg_width
FROM boxes
GROUP BY color;

fiddle