Build a dynamic pivot query with pairs of conditional SUM() statements with CodeIgniter

I am using codeigniter and I need to use aggregate functions in my query. I have this query.

"SELECT Dated,
    CASE WHEN `Account_ID` = 2 then SUM(Total_Bricks) ELSE 0 end as 'Nadeem', 
    CASE WHEN Account_ID = 2 then SUM(Kaat_Bricks) ELSE 0 end as 'NadeemKaat', 
    CASE WHEN `Account_ID` = 7 then SUM(Total_Bricks) ELSE 0 end as 'Abid', 
    CASE WHEN Account_ID = 7 then SUM(Kaat_Bricks) ELSE 0 end as 'AbidKaat', 
    CASE WHEN `Account_ID` = 8 then SUM(Total_Bricks) ELSE 0 end as 'Sajid', 
    CASE WHEN Account_ID = 8 then SUM(Kaat_Bricks) ELSE 0 end as 'SajidKaat' 
FROM `tblstockdetail` GROUP BY `Dated`"

i have generated this query through a simple foreach loop

$stock = $this->Kharkaar_Model->get_stockdetail();

$sql = '"SELECT Dated, ';

$numItems = count($stock);
$i = 0;
foreach ($stock as $key => $value) {
    if (++$i === $numItems) {
        $sql.= "CASE WHEN `Account_ID` = ".$value['Account_ID']." then SUM(Total_Bricks) ELSE 0 end as '".$value['AccountName']."', <br /> 
            CASE WHEN Account_ID = ".$value['Account_ID']." then SUM(Kaat_Bricks) ELSE 0 end as '".$value['AccountName']."Kaat' <br /> FROM `tblstockdetail` GROUP BY `Dated`";
    } else {
        $sql.= "CASE WHEN `Account_ID` = ".$value['Account_ID']." then SUM(Total_Bricks) ELSE 0 end as '".$value['AccountName']."', <br /> 
            CASE WHEN Account_ID = ".$value['Account_ID']." then SUM(Kaat_Bricks) ELSE 0 end as '".$value['AccountName']."Kaat', <br /> ";
    }   
}
$sql.= '"';

Now when I try to get result of this query with:

$result = $this->db->query($sql);

it is giving me a syntax error, otherwise when I put this query direct into:

$result = $this->db->query(// string query here );

...it is running fine.

Your foreach should be like this

$stock = $this->Kharkaar_Model->get_stockdetail();

$sql = "SELECT Dated, ";

$numItems = count($stock);
$i = 0;
foreach ($stock as $key => $value) 
{

    if(++$i === $numItems)
    {
        $Account_ID = $value['Account_ID'];
        $AccountName = $value['AccountName'];

        $sql.= "CASE WHEN `Account_ID` =  $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName, 
        CASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName 
        FROM `tblstockdetail` GROUP BY `Dated`";
    }
    else
    {
        $sql.= "CASE WHEN `Account_ID` = $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName,
        CASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName";

    }

}

if TRUE

"SELECT Dated, 
CASE WHEN `Account_ID` =  $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName, 
CASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName 
FROM `tblstockdetail` GROUP BY `Dated`"

if FALSE

"SELECT Dated, 
CASE WHEN `Account_ID` = $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName,
ASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName"

---

Wrong in your code

1. Wrapping too much with ' and "
2. <br /> don't use irrelevant tags

---

Suggestion

1. Use Variable ($Account_ID) instead of actual array pointer ($value['Account_ID'];) - It's EASY to UNDERSTAND and easy to DEBUG