Please Help me in merging two mysql queries, since I do not have enough skill in sql programming.
I think this is different with pivoting table in MySQL pivot table because in that case we still have to state the case statement manually ex : WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2014-01-01' , WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2014-07-01' this is something that I have done.
With the first query, I need to state the condition manually every time new 'mulai' data inserted, for example if a new 'mulai' data 2016-01-01 inserted, I have to add new CASE like this WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2016-01-01' in First query.
First query :
SELECT
nip,
nama,
DATE_FORMAT(t.tgl_grade_terakhir, '%d-%m-%Y') AS tgl_grade_terakhir,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2012-01-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem1_2012,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2012-07-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem2_2012,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2013-01-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem1_2013,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2013-07-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem2_2013,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2014-01-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem1_2014,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2014-07-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem2_2014,
(CASE
WHEN DATE_FORMAT(t.mulai, '%Y-%m-%d') = '2015-01-01' THEN t.nilaitalenta
ELSE NULL
END) AS sem1_2015
FROM (SELECT
nama,
mulai,
tgl_grade_terakhir,
talenta AS nilaitalenta,
nip
FROM history_talenta) AS t
I hope that I can do an automation in case statement with this query, but I don't have any idea about the implementation.
SELECT DISTINCT (
mulai
)
FROM history_talenta
WHERE mulai <> '0000-00-00'
ORDER BY mulai ASC
which return result like this :
