MySQL Forums
Forum List  »  Newbie

MySql query presented as Pivot
Posted by: Anders Grøndahl
Date: May 28, 2019 02:56AM

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?

Options: ReplyQuote


Subject
Written By
Posted
MySql query presented as Pivot
May 28, 2019 02:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.