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