I'm wondering how to make a pivot table that contains time series data where each column represents a different product. In general, the column names would be:
date | var1 | var2 | var3 | (etc...)
For example, if we have the below data:
date price discount_rate maturity_date
9/15/2013 45 3 2013
9/15/2013 46 3 2014
9/16/2013 47 3 2013
9/16/2013 41 2 2014
Then we would have three distinct products name following the naming convention
*var*_*discount*_*rate_maturity_date*
each as it's own column heading, with the first column being the date. The first part of the name, series, would be hardcoded but the rest of the name should be built based on the values in the distinct records. For example, the data in the above table would be returned in the form:
date series_3_2013 series_3_2014 series_3_2_2014
9/15/2013 45 46 NA
9/16/2013 47 NA 41
Is a query like this feasible to write in PostgreSQL? Thanks for any suggestions.
