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

Question

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.

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 '"SELECT Dated, CASE WHEN Account_ID = 2 then SUM(Total_Bricks) ELSE 0 end as '' at line 1 — Shafat Ahmad
– Shafat Ahmad, Commented Apr 3, 2017 at 10:43
print the $sql before execution and see what query you get run that query manually to check errors — Mudassar Khani
– Mudassar Khani, Commented Apr 3, 2017 at 11:00
my query is 100% fine..the issue is when i save this query in variable and pass that variable to query function then it gives error — Shafat Ahmad
– Shafat Ahmad, Commented Apr 3, 2017 at 11:35

Abdulla Nilam · Accepted Answer · 2017-04-03 11:51:42Z

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

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

Suggestion

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

mickmackusa · Accepted Answer · 2025-06-21 07:58:40Z

This is a great opportunity to dynamically generate a query via active record methods and avoid manual/hardcoded quoting.

Because you are iterating the 2d $stock array, you can use nested array_walk() iteration to avoid redundant code.

Notice that this solution doesn't need to increment&check a counter variable.

Also, I've never seen SUM() inside of CASE like you are doing it, so I'm going to assume that you mean for the CASE to be inside of SUM().

The following will provide the desired dynamic pivot query with two SUM() statements per stock row.

function getStocksWithSummedBricks(): array
{
    $accId = $this->db->escape_identifiers('Account_ID');
    $columnsAndAliases = array_combine(
        $this->db->escape_identifiers(['Total_Bricks', 'Kaat_Bricks']),
        ['total', 'kaat']
    );

    $this->db->select('Dated');

    array_walk(
        $this->Kharkaar_Model->get_stockdetail(),
        fn($stockRow) => array_walk(
            $columnsAndAliases,
            fn($aliasSuffix, $column) => $this->db->select(
                sprintf(
                    "SUM(CASE WHEN %s = %s THEN %s ELSE 0 END) %s",
                    $accId,
                    $this->db->escape($stockRow['Account_ID']),
                    $column,
                    $this->db->escape_identifiers($stockRow['AccountName'] . '_' . $aliasSuffix)
                ),
                false
            )
        )
    );

    return $this->db
        ->group_by('Dated')
        ->get('tblstockdetail')
        ->result_array();
}

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related