Click here to Skip to main content
15,881,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi friends, i am trying 2 days i didnt get a pivot table in mysql codeigniter. pls help me.
i got an error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Writing THEN qty ELSE 0 END) AS English - Cursive Writing ,max(case when' at line 1

What I have tried:

 $hasil = $this->db->query('SET SESSION group_concat_max_len=100000');
        $hasil = $this->db->query('SET @sql=(SELECT GROUP_CONCAT(DISTINCT concat("max(case when s.item_name=", item_name, " THEN qty ELSE 0 END) AS ", item_name, " ")
) FROM tbl_iss_books_dtl d INNER JOIN tbl_stationary s ON s.item_code=d.stat_item_code)');
        $hasil = $this->db->query('SET @pivot_statement = CONCAT("SELECT ibh_id,",@sql," from tbl_iss_books_dtl d INNER JOIN tbl_stationary s ON s.item_code=d.stat_item_code group by ibh_id order by ibh_id asc")');
        $hasil = $this->db->query('PREPARE complete_pivot_statement FROM @pivot_statement');
        $hasil = $this->db->query('EXECUTE complete_pivot_statement');
        return $hasil->result_array();
Updated 1-Jun-22 6:18am
Richard MacCutchan 1-Jun-22 12:16pm    
Start by displaying the actual SQL statement in error so you can actually see what is wrong.

1 solution

It looks like you are trying to create a column in your result set called English - Cursive Writing. That is an invalid column name. You need to surround it with the appropriate delimiters which in MariaDb I believe is ` i.e.
`English - Cursive Writing`
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900