PHP - SQL: Combine two rows into single row with multiple columns

Question

O/pI want to join two rows into single row with multiple columns(dynamically) using SQL Query in PHP.

SQL Table tf4i

Desired Output

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.

D-Shih · Accepted Answer · 2018-09-14T23:03:04.267

1

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;

sqlfiddle

edited Sep 14 '18 at 23:03

answered Sep 14 '18 at 15:11

D-Shih

44,943
6
31
51

It is working but Product needs to be fetched dynamically. Any idea for that by using php – Sri Vathsan Sep 14 '18 at 15:55
@SriVathsan You can try to use dynamic pivot https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – D-Shih Sep 14 '18 at 15:56
I have tried but i didn't understand it. could you please help me – Sri Vathsan Sep 14 '18 at 16:03
I have added O/P error of result. Could you please figure it out – Sri Vathsan Sep 14 '18 at 16:46
Any idea how to add this to php. – Sri Vathsan Sep 14 '18 at 16:51

PHP - SQL: Combine two rows into single row with multiple columns

1 Answers1