Warm tip: This article is reproduced from stackoverflow.com, please click
group-by oracle pivot sql oracle11g

Split column into multiple columns by criteria

发布于 2020-03-27 15:47:29

I have a query like the following:

select
  table.date,
  table.shift,
  sum(table.value)
from
  db.table
where
  table.date >= date '2020-01-01' and
  table.filter = 'type'
group by
  table.date,
  table.shift
order by
  table.date,
  table.shift;

That returns data this way:

date       | shift | sum(value)
-----------|-------|------------
2020-01-06 | 1     | 15
2020-01-06 | 3     | 12
2020-01-07 | 1     | 20
2020-01-07 | 2     | 38
2020-01-09 | 1     |  6
2020-01-09 | 2     | 22
2020-01-09 | 3     | 14
2020-01-10 | 1     | 17
2020-01-10 | 2     |  3
2020-01-10 | 3     | 10

I'm trying to get it like this but I don't know how:

date       | 1  | 2  | 3
-----------|----|----|----
2020-01-06 | 15 |    | 12
2020-01-07 | 20 | 38 |
2020-01-09 |  6 | 22 | 14
2020-01-10 | 17 |  3 | 10
Questioner
user7393973
Viewed
111
GMB 2020-01-31 17:20

No need for an addition subquery or CTE. You can pivot your dataset using conditional aggregation with slight modifications of your query: just remove shift from the group by clause, and then implement conditional logic in the sum()s:

select
  date,
  sum(case when shift = 1 then value end) shift1,
  sum(case when shift = 2 then value end) shift2,
  sum(case when shift = 3 then value end) shift3
from
  db.table
where
  date >= date '2020-01-01'
  and filter = 'type'
group by date
order by date

Note:

  • there is no need to prefix the column names since a single table comes into play. I removed those

  • date is the name of datatype in Oracle, hence not a good choice for a column name