O/pI want to join two rows into single row with multiple columns(dynamically) using SQL Query in PHP.
Output should be grouped by tf4hid and date.
SQL Query
Select tf4hid,tf4iid,date,product,planqty
from tf4i
group by date,product
This prints row by row but i need to generate columns based on the available products in the columns.
You can try to use condition aggregate function.
SELECT tf4hid,
date,
MAX(CASE WHEN product = 'MS(EURO-IV)' THEN planqty END) 'MS(EURO-IV)',
MAX(CASE WHEN product = 'HSD(Euro-IV)' THEN planqty END) 'HSD(Euro-IV)',
MAX(CASE WHEN product = 'Super_Petrol' THEN planqty END) 'Super_Petrol'
FROM tf4i
GROUP BY tf4hid,date
EDIT
dynamic pivot version. create the sql then execute dynamically.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' MAX(CASE WHEN product = ''',
product,
''' THEN planqty END) ''',
product , ''''
)
) INTO @sql
FROM tf4i;
SET @sql = CONCAT('SELECT tf4hid,
date, ', @sql, '
FROM tf4i
GROUP BY tf4hid,date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
