CODEWITHSUNDEEP
postgresql
Jerry
Jerry

Reputation: 41

Postgress SQL list gender types with values on the same row

I have the following table:

SELECT name, location_id, store_date, type, fact_count FROM table_test ORDER BY name, store_date; 

  name    | location_id | store_date | type | fact_count 
 Paris    |         466 | 2015-12-01 |    0 |        255
 Paris    |         466 | 2015-12-01 |    1 |        256
 Berlin   |         329 | 2015-12-01 |    1 |        248
 Berlin   |         329 | 2015-12-01 |    0 |        244
 Prague   |         201 | 2015-12-01 |    1 |        107
 Prague   |         201 | 2015-12-01 |    0 |        102

How can list type + value on the same row (I have always only 2 types)?

  name    | location_id | store_date | type_0 | fact_count_for_type_0 | type_1 | fact_count_for_type_1
 Paris    |         466 | 2015-12-01 |    0 |        255              |    1   |       256
 Berlin   |         329 | 2015-12-01 |    0 |        244              |    1   |       248
 Prague   |         201 | 2015-12-01 |    0 |        102              |    1   |       107

Upvotes: 1

Views: 83

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125374

SELECT 
    name, 
    location_id, 
    store_date, 
    sum(fact_count * (type = 0)::int) as fact_count_type_0,
    sum(fact_count * (type = 1)::int) as fact_count_type_1
FROM table_test
group by 1,2,3
ORDER BY name, store_date;

Upvotes: 1

Related Questions