Pivot-table style price matrix with Postgres

Question

How would you implement a price matrix like you see on airfare search websites using Postgres? Essentially, I would like to show the minimum prices for items by category. The items could be anything, but let's assume we're dealing with products that have various attributes, like color or size.

Our products table might look something like this:

id name price color size 1 Test 1 99 blue medium 2 Test 2 89 red small 3 Test 3 109 blue large 4 Test 4 79 blue small

On one axis we'd show the colors, and size on the other. The values in the table would show the minimum price for that color/size combo, so the cell at the intersection of blue and small would be 79.

How do you implement this, which looks a lot like a pivot table?

Answer 1

with t(id,name,price,color,size) as (
  values (1,'Test 1',99,'blue','medium')
  ,      (2,'Test 2',89,'red','medium')
  ,      (3,'Test 3',109,'blue','large')
  ,      (4,'Test 4',79,'blue','medium')
  ,      (5,'Test 5',99,'blue','medium')
  ,      (6,'Test 6',69,'blue','small')
  ,      (7,'Test 7',107,'blue','large')
)
select DISTINCT on (color,size) id, color, size, price
from t
  ORDER BY  color,size,price