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

Clockwork-Muse
– Clockwork-Muse

2018-10-08 18:53:27 +00:00
Commented Oct 8, 2018 at 18:53 — Clockwork-Muse
– Clockwork-Muse, Commented Oct 8, 2018 at 18:53

jagath · Accepted Answer · 2018-10-08 18:48:40Z

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 8, 2018 at 18:48

answered Oct 8, 2018 at 18:43

jagath

2381 silver badge6 bronze badges

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

5 Comments

Muhammad Iqbal Over a year ago

Hi, Thanks for help i got this error "aggregate function calls cannot be nested"

jagath Over a year ago

Please try the updated query which has only upstream sum (which has no nested aggregation)

Muhammad Iqbal Over a year ago

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.

jagath Over a year ago

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!

Muhammad Iqbal Over a year ago

Thanks bro. It worked.. I just changed the max keyword to sum... you saved my Day.. :)

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-08 18:49:59Z

1

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

edited Oct 8, 2018 at 18:49

answered Oct 8, 2018 at 18:33

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

PostgreSQL transpose rows in to columns

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related