select
  `tabel1`.`nip` AS `nip`,
  date_format(`tabel1`.`tgl`, '%d-%m-%Y') AS `tanggal`,
  year (`tabel1`.`tgl`) AS `tahun`,
  (case
     when (month (`tabel1`.`tgl`) < 4) then 'p1'
     when (month (`tabel1`.`tgl`) < 7) then 'p2'
     when (month (`tabel1`.`tgl`) < 10) then 'p3'
     when (month (`tabel1`.`tgl`) < 13) then 'p4'
   end) AS `periode`,
  max((case
         when (`tabel1`.`no` = 1) then `tabel1`.`nilai`
       end)) AS `s1`,
  max((case
         when (`tabel1`.`no` = 2) then `tabel1`.`nilai`
       end)) AS `s2`,
  max((case
         when (`tabel1`.`no` = 3) then `tabel1`.`nilai`
       end)) AS `s3`,
  max((case
         when (`tabel1`.`no` = 4) then `tabel1`.`nilai`
       end)) AS `s4`,
  max((case
         when (`tabel1`.`no` = 5) then `tabel1`.`nilai`
       end)) AS `s5`,
  max((case
         when (`tabel1`.`no` = 6) then `tabel1`.`nilai`
       end)) AS `s6`,
  max((case
         when (`tabel1`.`no` = 7) then `tabel1`.`nilai`
       end)) AS `s7`,
  max((case
         when (`tabel1`.`no` = 8) then `tabel1`.`nilai`
       end)) AS `s8`,
  max((case
         when (`tabel1`.`no` = 9) then `tabel1`.`nilai`
       end)) AS `s9`,
  max((case
         when (`tabel1`.`no` = 12) then `tabel1`.`nilai`
       end)) AS `s10`,
  max((case
         when (`tabel1`.`no` = 13) then `tabel1`.`nilai`
       end)) AS `s11`,
  max((case
         when (`tabel1`.`no` = 13) then `tabel1`.`nilai`
       end)) AS `s12`
from
  `tabel1`
group by
  `tabel1`.`nip`,
  date_format(`tabel1`.`tgl`, '%d%m%Y')
order by
  `tabel1`.`tgl`

Pivot tabel yang lebih detail bisa ke link ini https://jagowebdev.com/pivot-table-dengan-mysql/