mysqlsqldatabasepivot

MySQL Pivot turn record to column with all data listed

I have this kind of table.

svcid hostid fieldname      fieldval                           date_chk
  1    9205    rawdata      raw data                           2018-07-27 05:14:47
  1    9205    rawdata      raw data                           2018-07-27 05:14:57
  1    9205    rawdata      raw data                           2018-07-27 05:20:24
  1    9205    dummyoutput  echo "you have running dummy.sh!"  2018-07-27 05:20:24
  1    9205    rawdata      raw data                           2018-07-27 05:21:04
  1    9205    dummyoutput  echo "you have running dummy.sh!"  2018-07-27 05:21:04

And I want to turn into this

svcid hostid  rawdata    dummyoutput                           date_chk
  1    9205   raw data   null                                  2018-07-27 05:14:47
  1    9205   raw data   null                                  2018-07-27 05:14:57
  1    9205   raw data   echo "you have running dummy.sh!"     2018-07-27 05:20:24
  1    9205   raw data   echo "you have running dummy.sh!"     2018-07-27 05:21:24

For future information I already created a SQL fiddle here

Thanks!

Solution

  • You can try this. Aggregate function condition

    use CASE WHEN with MAX function and group by

    SELECT svcid,
       hostid,
       max(CASE WHEN fieldname = 'rawdata' THEN fieldval  END), 
       max(CASE WHEN fieldname = 'dummyoutput' THEN fieldval  END),
       date_chk
FROM tb_service_out_monitor
GROUP BY  svcid,
       hostid,
       date_chk

    sqlfiddle