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
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
This answer is the one that better fits my needs so I'll accept it. Wasn't expecting this much of a fastest gun in the west, more like close as duplicate of some question I didn't found or maybe a "have you searched?" sort of accusation. Thank you.