Return to Answer

• PostgreSQL Crosstab Query

• PostgreSQL Crosstab Query

I suggest a radially new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions:

• We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.

• JOIN that to a set of dates you are interested in (today, yesterday, ...). One row per date.

• LEFT JOIN to project_reports to attach the position for each date if available. Default to 0 with COALESCE.

• Cross-tabulate to get a single row with one position column per given date.

• Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.
  Aside: "name" is not a good name. I'd use something descriptive instead.

Can't provide a fiddle, installing additional modules is not allowed there.

I suggest a radically new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions:

• We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.

• JOIN that to a set of dates you are interested in (today, yesterday etc.). One row per date.

• LEFT JOIN to project_reports to attach the position for each date if available. Default to 0 with COALESCE.

• Cross-tabulate to get a single row with one position column per given date.

• Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.

Aside: "name" is not a good name. I'd use something descriptive instead.

• We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.

• JOIN that to a set of dates you are interested in (today, yesterday, ...). One row per date.

• LEFT JOIN to project_reports for said days.

• Cross-tabulate to get one column with the position per date.

• Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.

• We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.

• JOIN that to a set of dates you are interested in (today, yesterday, ...). One row per date.

• LEFT JOIN to project_reports to attach the position for each date if available. Default to 0 with COALESCE.

• Cross-tabulate to get a single row with one position column per given date.

• Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.
  Aside: "name" is not a good name. I'd use something descriptive instead.