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.

Improve this question
4
  • 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: stackoverflow.com/questions/28087948/… Commented Jan 6, 2023 at 21:30
  • Thank you, this indeed is a use-case almost identical to mine: stackoverflow.com/questions/28087948/… Commented Jan 6, 2023 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) Commented Jan 6, 2023 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 Commented Jan 6, 2023 at 22:37

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Great, thanx. As someone above suggested, this question has a use-case almost identical to mine: stackoverflow.com/questions/28087948/… Indeed crosstab is the answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.