MySQL pivot row run in sql fiddle but not work in syntax model codeigniter

Question

i use code to use MySQL pipot row at this link MySQL pivot row into dynamic number of columns it's work on query editor and SQL fidle. but it's not work when i use query to model of codeIgniter

in Model PHP on Code Igniter i use code like this:

$query=$this->db->query("
   SET @sql = NULL;
   SELECT
   GROUP_CONCAT(DISTINCT
   CONCAT(
     'count(case when tahun = ''',
     tahun,
     ''' then 1 end) AS tahun_',
     replace(tahun, ' ', '')
   )
   ) INTO @sql
   from tb_tahun;

   SET @sql = CONCAT('SELECT pt.jenis_perjal, ', @sql, ' from tb_jenis_perjal pt
   left join tb_kebutuhan_perjal s
     on pt.id_jenis_perjal = s.id_jenis_perjal
   left join tb_tahun pd
     on s.id_tahun = pd.id_tahun
   group by pt.jenis_perjal');

   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
");

result of query :

Err Number : 1064 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT( 'count(case when' at line 2

score 0 · Answer 1 · answered Jun 06 '17 at 04:54

Try saving the text in a variable and pass that variable as parameter to run method

    $var = "SET @sql = NULL;
       SELECT
       GROUP_CONCAT(DISTINCT
       CONCAT(
         'count(case when tahun = ''',
         tahun,
         ''' then 1 end) AS tahun_',
         replace(tahun, ' ', '')
       )
       ) INTO @sql
       from tb_tahun;

       SET @sql = CONCAT('SELECT pt.jenis_perjal, ', @sql, ' from tb_jenis_perjal pt
       left join tb_kebutuhan_perjal s
         on pt.id_jenis_perjal = s.id_jenis_perjal
       left join tb_tahun pd
         on s.id_tahun = pd.id_tahun
       group by pt.jenis_perjal');

       PREPARE stmt FROM @sql;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;";

     $query=$this->db->query($var);

MySQL pivot row run in sql fiddle but not work in syntax model codeigniter

1 Answers1