CODEWITHSUNDEEP
mysql
06011991
06011991

Reputation: 807

mysql/pivot table with two tables

i was trying combined multiple rows into single row with different columns but its giving different way.here is my data .

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   |

table2:

| 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%        |

and desired output should be like as follows

| 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        |

here is my query but its giving result with comma separated duedates.i dnt wnat like that.

    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

Please some body help me to sort it out.

Upvotes: 0

Views: 346

Answers (1)

iLikeMySql
iLikeMySql

Reputation: 746

Don't try to solve this in one statement without advanced knowledge. Make a script and split it into several steps. It will run really quickly if you do it right(usually faster than the single-query approach).

Steps could be:

Create table 3 which is table 2 enriched with data from table 1 and order by name and due_date .
Add a column to number the occurrences per name by adding a column that is auto_increment and part of a Unique Key together with name:

ALTER TABLE TABLE_3
ADD COLUMN position_id integer auto_increment NOT NULL,
ADD Unique Key positon_number(Name, position_id);

After that you can refer to the first, second etc. occurrence and related duedate and dueamount by using position_id:

select
name, invoice_value, invoice_date, #

if(position_id=1, duedate ,null) as due_date_1,
if(position_id=1, amount ,null) as due_amount_1,

if(position_id=2, duedate ,null) as due_date_2,
if(position_id=2, amount ,null) as due_amount_2,

if(position_id=3, duedate ,null) as due_date_3,
if(position_id=3, amount ,null) as due_amount_3,

if(position_id=4, duedate ,null) as due_date_4,
if(position_id=4, amount ,null) as due_amount_4,

if(position_id=5, duedate ,null) as due_date_5,
if(position_id=5, amount ,null) as due_amount_5

FROM table_3
GROUP BY name;

However it would be better to use the customer id instead of the name because you would have to look out for duplicate names otherwise.

Upvotes: 1

Related Questions