PIVOT query in Mysql

Question

I have a query in SQL Server using PIVOT that returns the data in column as image below:

The query is this:

SELECT * FROM(
          SELECT ftd.DateAndTime AS dt,  ftd.Val AS Val, CONVERT(VARCHAR, ttd.TagIndex) as tagIndex 
          FROM FloatTableHour ftd INNER JOIN TagTableHour ttd ON ttd.TagIndex = ftd.TagIndex  
          WHERE ftd.Marker = 'S' 
          AND ftd.DateAndTime BETWEEN '2019-01-14 00:00:000' AND '2019-01-14 23:59:059' 
          AND ttd.TagName IN(
                            '[AGUA]FT[0].Tot_NR', 
                            '[AGUA]FT[0].Tot_D', 
                            '[AGUA]POCO[0].IntTotPrdHou', 
                            '[AGUA]POCO[0].IntTotPrdMin', 
                            '[AGUA]POCO[0].OutEfePrd', 
                            '[AGUA]POCO[0].OutEfePrdMin', 
                            '[AGUA]LT_MAX_DA[0]', '[AGUA]LT_MIN_DA[0]')) 
                            teste PIVOT(SUM(Val) FOR tagIndex in([9], [8], [16], [17], [30], [18], [0], [4]) ) colunas 
            ORDER BY dt

However, due to the limitation of the 10GB database, I am migrating to Mysql and need to perform the same query, I was able to do so to this point:

SELECT ftd.DateAndTime AS dt,  ftd.Val AS Val, ttd.TagIndex as tagIndex FROM FloatTableHour ftd 
            INNER JOIN TagTableHour ttd ON ttd.TagIndex = ftd.TagIndex 
            AND ftd.DateAndTime 
            BETWEEN '2019-01-14 00:00:000'
            AND '2019-01-14 23:59:059' 
            AND ttd.TagName IN(
                                '[AGUA]FT[0].Tot_NR', 
                                '[AGUA]FT[0].Tot_D', 
                                '[AGUA]POCO[0].IntTotPrdHou', 
                                '[AGUA]POCO[0].IntTotPrdMin', 
                                '[AGUA]POCO[0].OutEfePrd', 
                                '[AGUA]POCO[0].OutEfePrdMin', 
                                '[AGUA]LT_MAX_DA[0]', 
                                '[AGUA]LT_MIN_DA[0]')

But your return is as in the image:

Answer 1

Maybe so?

SELECT      ftd.DateAndTime                                             AS dt
        ,   SUM(CASE WHEN ttd.TagIndex = 9 THEN ftd.Val ELSE 0 END)     AS '9'
        ,   SUM(CASE WHEN ttd.TagIndex = 8 THEN ftd.Val ELSE 0 END)     AS '8'
        ,   SUM(CASE WHEN ttd.TagIndex = 16 THEN ftd.Val ELSE 0 END)    AS '16'
        ,   SUM(CASE WHEN ttd.TagIndex = 17 THEN ftd.Val ELSE 0 END)    AS '17'
        ,   SUM(CASE WHEN ttd.TagIndex = 30 THEN ftd.Val ELSE 0 END)    AS '30'
        ,   SUM(CASE WHEN ttd.TagIndex = 18 THEN ftd.Val ELSE 0 END)    AS '18'
        ,   SUM(CASE WHEN ttd.TagIndex = 0 THEN ftd.Val ELSE 0 END)     AS '0'
        ,   SUM(CASE WHEN ttd.TagIndex = 4 THEN ftd.Val ELSE 0 END)     AS '4'
FROM        FloatTableHour  ftd 
INNER JOIN  TagTableHour    ttd ON  ttd.TagIndex = ftd.TagIndex 
WHERE       ftd.DateAndTime BETWEEN '2019-01-14 00:00:000' AND '2019-01-14 23:59:059' 
        AND ttd.TagName IN  (
                                '[AGUA]FT[0].Tot_NR', 
                                '[AGUA]FT[0].Tot_D', 
                                '[AGUA]POCO[0].IntTotPrdHou', 
                                '[AGUA]POCO[0].IntTotPrdMin', 
                                '[AGUA]POCO[0].OutEfePrd', 
                                '[AGUA]POCO[0].OutEfePrdMin', 
                                '[AGUA]LT_MAX_DA[0]', 
                                '[AGUA]LT_MIN_DA[0]'
                            )
GROUP BY    ftd.DateAndTime