MySql query presented as Pivot
We have table with data like this:
# verdi_id, datatype_id, sted_id, datotid, verdi
'3385', '1', 'KD01', '30.04.2019 13:05:00', '6.75'
'3386', '2', 'KD01', '30.04.2019 13:05:00', '10'
'3387', '3', 'KD01', '30.04.2019 13:05:00', '4.4'
'3388', '4', 'KD01', '30.04.2019 13:05:00', '6.24'
'3389', '5', 'KD01', '30.04.2019 13:05:00', '54.6'
'3390', '1', 'KD01', '30.04.2019 13:10:00', '6.75'
'3391', '2', 'KD01', '30.04.2019 13:10:00', '10'
'3392', '3', 'KD01', '30.04.2019 13:10:00', '4.4'
'3393', '4', 'KD01', '30.04.2019 13:10:00', '6.24'
'3394', '5', 'KD01', '30.04.2019 13:10:00', '54.6'
We want to presentet it as Pivot output. We have this query:
select (datotid),
case when datatype_id =1 then verdi else 0 end as pHned,
case when datatype_id =2 then verdi else 0 end as Temp,
case when datatype_id =3 then verdi else 0 end as cbm,
case when datatype_id =4 then verdi else 0 end as pHopp,
case when datatype_id =5 then verdi else 0 end as Nivaa
from sokna_web.verdier where verdi_id >= (select max(verdi_id)-10080 from sokna_web.verdier)
and datatype_id IN (1,2,3,4,5) and right(datotid,5)in ("00:00","30:00") and verdi_id > 3384
It gives us this result:
# datotid, pHned, Temp, cbm, pHopp, Nivaa
'21.05.2019 10:30:00', '6.440000057220459', '0', '0', '0', '0'
'21.05.2019 10:30:00', '0', '14.5', '0', '0', '0'
'21.05.2019 10:30:00', '0', '0', '3.799999952316284', '0', '0'
'21.05.2019 10:30:00', '0', '0', '0', '6.170000076293945', '0'
'21.05.2019 10:30:00', '0', '0', '0', '0', '48.599998474121094'
We want all datatype_id on one line in column pr same date/time stamp.
Anybody who can help us?