I have a table by name material transaction and I want to display data like Table B

I want result like below table Table B

$query = "SELECT item_name, SUM(material_wt) AS issue_material WHERE transaction_type = 'Issue Material' FROM material_transaction GROUP BY item_name UNION ALL SELECT item_name, SUM(material_wt) AS return_material WHERE transaction_type = 'Reject Material' FROM material_transaction GROUP BY item_name";
                        $select_query_table = mysqli_query($connection,$query);
                        while($run_select_query = mysqli_fetch_assoc($select_query_table)){
                 
                     $item_name = $run_select_query['item_name'];  
                     $wt = $run_select_query['material_wt'];  
                        echo "<td>$item_name</td>";                          
                       echo "<td>$wt</td>";                          
                        echo "</tr>";
                   
                    }

CodePudding user response：

You may use conditional aggregation:

SELECT
    item_name,
    SUM(CASE WHEN transaction_type = 'Issue Material'
             THEN Weight ELSE 0 END) AS Issue_Material,
    SUM(CASE WHEN transaction_type = 'Return Material'
             THEN Weight ELSE 0 END) AS Return_Material
FROM material_transaction
GROUP BY item_name;