Postgresql transpose rows to columns

Question

I have this query

select * from sales

       shop |    date    |    hour   | row_no | amount
 -----------+------------+-----------+--------+-----------
     shop_1 | 2012-08-14 | 00:08:00  | P01    | 10
     shop_2 | 2012-08-12 | 00:12:00  | O05    | 40
     shop_2 | 2012-08-12 | 00:12:00  | A01    | 20

I have 1 millon rows, I can do this query

select shop, SUM(amount) 
from sales 
group by shop

       shop |   amount   |    
 -----------+------------+
     shop_1 |   5666     |  
     shop_2 |   4044     |  
     shop_3     4044     |

But I need to spend the days at the columns and I do not know if they could help me do this

       shop |    2012-08-1    |    2012-08-2   | 2012-08-3 |
 -----------+------------+-----------+--------+-----------
     shop_1 |      4005       |      5667     |      9987  |     
     shop_2 |      4333      |      4554     |      1234  |     
     shop_3 |      4555       |      6778     |      6677 |

Would be group by store in the rows, and group by days in the columns in postgresql

Cross tab requires you to know the dimensions of the table produced. You won't know that unless you know how many days you've got or want. Do you? Do you want every day represented or just a week, because "every day" requires dynamic sql AND crosstab. — Evan Carroll, Apr 21 '17 at 17:03
Also check https://github.com/hnsl/colpivot which can discover the columns — Jerome WAGNER, Oct 18 '17 at 13:29

Chidi Ekuma · Answer 1 · 2017-04-24T09:24:30.130

0

First, you must install tablefunc extension. Since version 9.1 you can do it using create extension:

CREATE EXTENSION tablefunc;

select * from crosstab (  
    select shop, date, SUM(amount) 
    from sales 
    group by shop

    'select date from sales order by 1') 
AS ct(shop: text,  '2012-08-1' text, '2012-08-2' text, '2012-08-3' text)

edited Apr 24 '17 at 09:24

answered Apr 22 '17 at 06:45

Chidi Ekuma

2,811
2
19
30

score 0 · Answer 2 · answered Jul 15 '23 at 15:39

You can use crosstab to the achieve the desired results, here is a solution:

SELECT * FROM CROSSTAB(
  'SELECT shop, date, amount FROM sales ORDER BY 1,2',
  'SELECT DISTINCT date FROM sales ORDER BY 1'
) AS ct(shop text, "2012-08-12" int, "2012-08-14" int);

You can also check here: DbFiddle

Explanation: Crosstab function takes two arguments, a source query and a category query. Source query provides the data to be pivoted and category query provides the column names for pivot table. You can checkout the official documentation here: tableFunc.

crosstab ( source_sql text, category_sql text ) → setof record Produces a “pivot table” with the value columns specified by a second query.

Postgresql transpose rows to columns

2 Answers2