MySQL/Pivot table for two tables

MySQLPHP

I was trying to get multiple rows into a single row with dynamic columns created as per the data. I have two tables combined with foreign key.

Table 1:

| id | name | Invoice value | invoice_date |
|----|------|---------------|--------------|
| 1  | A    | 5000          | 30-01-2016   |
| 2  | B    | 8000          | 02-05-2016   |
| 3  | C    | 10000         | 03-05-2016   |

Table 2:

| id | invoice_id | duedate    | amount | percentage |
|----|------------|------------|--------|------------|
| 1  | 1          | 15-01-2016 | 2500   | 50%        |
| 2  | 1          | 30-01-2016 | 2500   | 50%        |
| 3  | 2          | 15-02-2016 | 8000   | 100%       |
| 4  | 3          | 15-05-2016 | 5000   | 50%        |
| 5  | 3          | 19-05-2016 | 2500   | 25%        |
| 6  | 3          | 25-05-2016 | 2500   | 25%        |

Desired output:

| name | invoice_value | invoice_date | due date1  | due amount1 | due date2  | due amount2 | due date3  | due amount3 |
|------|---------------|--------------|------------|-------------|------------|-------------|------------|-------------|
| A    | 5000          | 30-01-2016   | 15-01-2016 | 2500        | 30-01-2016 | 04-11-1906  | null       | null        |
| B    | 8000          | 02-05-2016   | 15-02-2016 | 8000        | null       | null        | null       | null        |
| C    | 10000         | 03-05-2016   | 15-05-2016 | 5000        | 19-05-2016 | 2500        | 19-05-2016 | 2500        |

When I tried have used group-concat for the multiple columns it's giving results with comma separated. But I want as desired output. Please somebody help to solve this issue how to write a query for this.

I was using the following query but it's giving results as comma separated result:

SELECT  T1.name,T1.invoice_value,T1.invoice_date,T1.duedate,T1.dueamount
    FROM  
      ( SELECT  table1.name , table1.invoice_value, table1.invoice_date,
               group_concat(table2.duedate1) as duedate,
               group_concat(table2.dueamount1) as dueamount
            FROM  table1
            LEFT JOIN  table2  ON table1.id=table2.invoice_id 
      )T1
    Group By  T1.id

Best Answer

(Technically, this is 'not an answer', but claim it needs to be said.)

There will be an arbitrary number of columns, correct? Then (1) the output is impractical, and (2) it would be virtually impossible to generate the output in SQL.

So, (1) rethink the requirement, then (2) consider whether to do it in PHP instead.

Related Question