0

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.

Maggie
  • 7,823
  • 7
  • 45
  • 66
  • 1
    If the number of columns is not know at design time, search for `dynamic pivot`. Have a look if any answers or links in them are helpful: https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3 – PM 77-1 Jan 06 '23 at 21:30
  • Thank you, this indeed is a use-case almost identical to mine: https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3 – Maggie Jan 06 '23 at 22:17
  • Just a quick comment -- Creating dates as columns is a "reporting" like action. Systems that do reporting are better at doing this. It might be that your front end reporting system is better suited to displaying this (or creating the bar chart or whatever) – Hogan Jan 06 '23 at 22:19
  • 1
    The SQL language has a strict rule that you **MUST** know how many columns you need at query compile time, before looking at **ANY** table data. If you can't do this, crosstab and PIVOT won't help you. Instead, you'll have to do it over three steps: `1)` Run a query to find out what columns you need. `2)` Use results from 1 to build an new SQL statement dynamically, with all the columns listed. `3)` Run the query built in 2... But really this kind of pivot is antithetical to the relational set theory behind modern databases, and is therefore best handled by the client code or reporting tool – Joel Coehoorn Jan 06 '23 at 22:37

1 Answers1

0

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.

Dani U
  • 399
  • 2
  • 6
  • Great, thanx. As someone above suggested, this question has a use-case almost identical to mine: https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3 Indeed crosstab is the answer – Maggie Jan 06 '23 at 22:17