POSTGRES transpose week columns into rows?

Question

My forecast table stores the current week, and then forecasted amounts for 26 weeks out:

CREATE TABLE forecast_listings (
   product    integer NOT NULL
 , current_week_date date 
 , weekplus0  integer NOT NULL DEFAULT 0
 , weekplus1  integer NOT NULL DEFAULT 0
 , weekplus2  integer NOT NULL DEFAULT 0
 , weekplus3  integer NOT NULL DEFAULT 0
 , weekplus4  integer NOT NULL DEFAULT 0
 -- etc
 , weekplus24 integer NOT NULL DEFAULT 0
 , weekplus25 integer NOT NULL DEFAULT 0
);

For example forecasting a single item, I select a single row with the most recent current_week_date and then look at relative weeks.

SELECT
unnest(
    array[
    to_char(week_current_date, 'YYYY-MM-DD'),
    to_char(week_current_date + interval '1 weeks', 'YYYY-MM-DD'),
    to_char(week_current_date + interval '2 weeks', 'YYYY-MM-DD'),
    to_char(week_current_date + interval '3 weeks', 'YYYY-MM-DD'),
    to_char(week_current_date + interval '4 weeks', 'YYYY-MM-DD')
    -- ...all the way to 25
    ]
) AS "Week",
unnest(
    array[
    weekplus0,
    weekplus1, 
    weekplus2, 
    weekplus3,
    weekplus4
    -- ...all the way to 25
    ]
) AS "Count"
FROM (
    SELECT * FROM forecast_listings 
    WHERE product_id = 1
    ORDER BY week_current_date DESC
    LIMIT 1
) as row

I would like to do this with Postgres, essentially fetching a row and transposing each weeks number to a row with a date column and count column:

week, count
2017-10-01,100
2017-10-08,200
2017-10-15,150
etc.

Please clarify. `asin`, `valid_as_of` (you mean `current_week_date`?), and `weekcurrent`(`weekplus0`?) are not in your table definition. And what's with the *"month"* in the title? I lent a hand and replaced the list of columns with a `CREATE TABLE` statement we can work with. And some syntax fixes. Provide some sample values and your version of Postgres. I am pretty sure there is an elegant solution ... — Erwin Brandstetter, Oct 05 '17 at 01:27
@ErwinBrandstetter yeah i have slimmed the question down to a simple what is a better way to transpose this weekly data from column values into separated rows. — Blair Anderson, Oct 05 '17 at 15:19

Erwin Brandstetter · Answer 1 · 2017-10-06T02:35:05.727

SELECT to_char(f.week_current_date + interval '1 week' * w, 'YYYY-MM-DD')
     , arr[w+1]
FROM  (
   SELECT week_current_date
        , ARRAY[weekplus0, weekplus1, weekplus2, weekplus3] AS arr  -- add all 26
   FROM   forecast_listings
   WHERE  product_id = 1
   ORDER  BY week_current_date DESC NULLS LAST  -- ?
   LIMIT  1
   ) f
CROSS  JOIN LATERAL generate_series(0, 25) w;

The key feature is a CROSS JOIN LATERAL to generate_series() to generate the desired rows. Use an ARRAY constructor like you already had in the subquery. The generated index w is used to add weeks to the base date as well as to access respective array items. Take note of a lurking off-by-1 error, since Postgres array indexes are 1-based by default. Related:

Since week_current_date does seem to allow NULL values, you may want to use ORDER BY week_current_date DESCNULLS LAST to sort rows with NULL last which would otherwise be on top. See:

PostgreSQL sort by datetime asc, null first?

POSTGRES transpose week columns into rows?

1 Answers1