Hi i have a mysql table like this:

How to make the table into one row(s) like below here?

Can I please also have the lines of code to change to this instead of just a suggestion? because I am new to MySQL. But still any help is appreciated!

My code so far:

SELECT DateTime, 
       SUM(CASE WHEN skynet_msa.testingintern.TagName LIKE 'Temp%' 
                THEN value 
                ELSE 0 END) as Temperature,
       SUM(CASE WHEN skynet_msa.testingintern.TagName LIKE 'HUMIDITY%' 
                THEN value 
                ELSE 0 END) as Humidity,
FROM skynet_msa.testingintern
GROUP BY DateTime 
LIMIT 0, 50000;

CodePudding user response：

The main issue of your query is that you're grouping on the "DateTime" field, which is equal for every row and does not identify the group you want. What identifies the group is really the combination of the first part of the "TagName" field and the "Datetime" together.

Since we can't use "TagName" directly, we can extract the interesting part (e.g. 'SGHAST_000<n>' from 'SGHAST_000<n>_<fieldname>') using MySQL SUBSTRING_INDEX function. When you pass a character (in our case _) and an index i (in our case 2), it will retrieve everything in the string between the first character and the ith character (in our case 'SGHAST_000<n>').

Once we are able to build our group, we can use that inside a GROUP BY clause, and use MySQL MAX aggregation function to extract the values of temperature and humidity when and only when these occur inside the "TagName" field.

SELECT SUBSTRING_INDEX(TagName, '_', 2) AS TagName,
       MAX(CASE WHEN TagName LIKE '%Temp'     
                THEN Value END        ) AS Temperature,
       MAX(CASE WHEN TagName LIKE '%HUMIDITY' 
                THEN Value END        ) AS Humidity
FROM testingintern
GROUP BY SUBSTRING_INDEX(TagName, '_', 2), 
         Datetime

You can play with it (and select what specific function returns) at this link.