Pivot data in PostgreSQL using two columns for row data

Question

In PostgreSQL I have a table country and a table property. One country can have a lot of properties. Each property have a created_at. I need count the properties for each country separating by the year and week. Like an example i have that query for one country.

SELECT 
  EXTRACT(YEAR FROM created_at) AS yy, 
  EXTRACT(WEEK FROM created_at) AS week, 
  COUNT(id) AS country_1
FROM property 
WHERE id_country = 1
GROUP BY week, yy 
ORDER BY yy, week ASC

The result of that is something like this

yy  |week|country_1
---------------
2014|1   |5
2014|2   |1154
2014|3   |769
...

So, i need a result like this

yy  |week|country_1|country_2|country_3
----------------------------------------
2014|1   |5        |56       |543
2014|2   |1154     |1234     |432
2014|3   |769      |123      |432
...

Is that possible?, how would be the query?

Are the country values few and known beforehand?

Patrick
– Patrick

2015-08-11 13:37:13 +00:00
Commented Aug 11, 2015 at 13:37 — Patrick
– Patrick, Commented Aug 11, 2015 at 13:37

Radek Postołowicz · Accepted Answer · 2015-08-11 14:25:57Z

2

Alternative solution without any extensions:

SELECT 
  EXTRACT(YEAR FROM created_at) AS yy, 
  EXTRACT(WEEK FROM created_at) AS week, 
  COUNT(CASE WHEN id_country = 1 THEN id ELSE null END) AS country_1,
  COUNT(CASE WHEN id_country = 2 THEN id ELSE null END) AS country_2,
  COUNT(CASE WHEN id_country = 3 THEN id ELSE null END) AS country_3,
  ...
  COUNT(CASE WHEN id_country = 7 THEN id ELSE null END) AS country_7,
FROM property 
GROUP BY week, yy 
ORDER BY yy, week ASC;

And if you are using pg 9.4 or later you could replace

COUNT(CASE WHEN id_country = 2 THEN id ELSE null END) AS country_2

with

COUNT(id) FILTER (WHERE id_country = 2) AS country_2

and so on.

answered Aug 11, 2015 at 14:25

Radek Postołowicz

4,8742 gold badges32 silver badges52 bronze badges

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

Comments

Patrick · Accepted Answer · 2015-08-11 14:13:16Z

You can solve this with the crosstab() function (in the tablefunc extension), which pivots your data. Unfortunately that function can pivot only on a single column, so you'll have to fudge your data a bit and then un-fudge it to get the desired result.

First the fudge, instead of producing two columns like this:

extract(year from created_at) AS yy, 
extract(week from created_at) AS week

you should put them in a single column like so:

extract(year from created_at) * 100 + extract(week from created_at) AS yr_wk

giving you values like 201401, 201402, 201403 etc. The nice property is that they sort just like separate year and week values and they are easy to assemble and later disassemble.

Next you run the crosstab() function:

SELECT * FROM crosstab(
  'SELECT extract(year from created_at) * 100 + extract(week from created_at) AS yr_wk,
          id_country, count(id) AS cnt
   FROM property GROUP BY 1, 2 ORDER BY 1, 2',
  'SELECT s FROM generate_series(1, 7) s'
  ) AS (yr_wk int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int);

where 'c1'-'c7' are the names of your countries.

And then to unfudge the yr_wk values you do:

SELECT yr_wk / 100 AS yy, yr_wk % 100 AS week, c1, c2, c3, c4, c5, c6, c7
FROM (
  << crosstab query from above >>
) sub
ORDER BY 1, 2;

Collectives™ on Stack Overflow

Pivot data in PostgreSQL using two columns for row data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related