PostgreSQL transpose rows in to columns

Question

i have one query

select department_id,SUM(quantity) as Quantity,sales_report.date as Date from sales_report where date = '2018-10-04' GROUP BY department_id , Date ORDER BY department_id ASC;

which gives me output like below:

id  quantity    date
1   204       2018-10-04
2   88        2018-10-04
3   135       2018-10-04
4   72        2018-10-04
5   391       2018-10-04
6   134       2018-10-04
7   386       2018-10-04
8   421       2018-10-04
9   292       2018-10-04
10  86        2018-10-04
11  83        2018-10-04
12  34        2018-10-04
13  3435      2018-10-04

but I need data like this:

id          1   2   3   4   5   6   7   8   9   10  11  12  13
2018-10-04  204 88  135 72  391 134 386 421 292 86  83  34  3435

Can anyone help me to achieve this..

Possible duplicate of [SQL Transpose Rows as Columns](https://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns) — Clockwork-Muse, Oct 08 '18 at 18:53

Zaynul Abadin Tuhin · Answer 1 · 2018-10-08T18:49:59.050

you need case when, from your output data set you could do this pivoting

   select date, max(case when id=1 then quantity end) as 1_one,
   max(case when id=2 then quantity end) as 2_one,
   max(case when id=3 then quantity end) as 3_one,
   max(case when id=4 then quantity end) as 4_one,
   max(case when id=5 then quantity end) as 5_one,
   max(case when id=6 then quantity end) as 7_one,
   max(case when id=7 then quantity end) as 7_one,

    max(case when id=10 then quantity  end) as 10_ten from t
    group by date

jagath · Accepted Answer · 2018-10-08T18:48:40.697

1

you can try this:

  select sales_report.date, 
  sum(case when id=1 then quantity else 0 end) as '1',
  sum(case when id=2 then quantity else 0 end) as '2',
  sum(case when id=3 then quantity else 0 end) as '3',
  sum(case when id=4 then quantity else 0 end) as '4',
  sum(case when id=5 then quantity else 0 end) as '5',
  sum(case when id=6 then quantity else 0 end) as '6',
  sum(case when id=7 then quantity else 0 end) as '7',
  sum(case when id=8 then quantity else 0 end) as '8',
  sum(case when id=9 then quantity else 0 end) as '9',
  sum(case when id=10 then quantity else 0 end) as '10',
  sum(case when id=11 then quantity else 0 end) as '11',
  sum(case when id=12 then quantity else 0 end) as '12',
  sum(case when id=13 then quantity else 0 end) as '13'
  from sales_report where date = '2018-10-04' GROUP BY Date ORDER BY date ASC

edited Oct 08 '18 at 18:48

answered Oct 08 '18 at 18:43

jagath

238
1
6

Hi, Thanks for help i got this error "aggregate function calls cannot be nested" – Muhammad Iqbal Oct 08 '18 at 18:52
Please try the updated query which has only upstream sum (which has no nested aggregation) – jagath Oct 08 '18 at 18:59
in this scenario id is foreign key that is repeated. and I want sum of the quantity.. is there any way I can do such task. – Muhammad Iqbal Oct 08 '18 at 19:07
yes, you can still do the same operation though it is a foreign key which doesn't impact the aggregation. Also, you could use the id column for table joins too though it is explicitly not displayed as the column values. Hope it helps! – jagath Oct 08 '18 at 19:11
Thanks bro. It worked.. I just changed the max keyword to sum... you saved my Day.. :) – Muhammad Iqbal Oct 08 '18 at 19:19

PostgreSQL transpose rows in to columns

2 Answers2