Create columns from rows in SQL-CodePudding

I have a table of historical prices of a product, that keeps track of product price changes over time, something like:

CREATE TABLE product
  product_id int
  price_date date
  price number

I would like to display data in the format:

[product_id] [2022-12-05] [2022-12-19] [2022-12-31] [2023-01-03]
    112          4.23        4.5          4.5          4.86
    113          3.98        3.91         5.39         5.45

ie. one row would contain the product id and the prices on each date, for however many dates there are. I don't know the number of distinct dates in advance. Most products will have updates on the same date; if there's a value missing, it's ok to display null for it. Is this even possible in SQL or is my best bet to do this in the application logic? I'm using PostgreSQL if that makes a difference.

CodePudding user response：

The Postgres Crosstab feature has already been covered here. Since the columns you want are dates, I suspect you need those to be dynamic. In that case, you're going to have to write yourself a function that reads the distinct values of price_date and executes a query string that's constructed on the fly.