sql - PostgreSQL 将行转置为列

Question

我有一个问题

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;

这给了我如下输出:

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

但我需要这样的数据:

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

任何人都可以帮助我实现这一点..

Answer 1

你可以试试这个:

  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