MySQL combine tables & transpose multiple columns with unique row id into non-unique rows

Question

I am trying to combine multiple tables into one and change the structure of the data set by transposing the tables from columns to rows. I am using MySQL.

I've tried now couple of days to find a smart solution without manually transposing each row with uniqued ID into multiple rows with same id. However, I'm finding myself stuck.

My current data set:

Table one:

ID    M1 amount    M2 amount    M3 amount
1234  $400 543,00  $600 000,00  $500 321,00 
4566  $500 321,00  $300 012,00  $200 345,00 
3311  $300 000,00  $450 000,00  $100 312,00 

Table two:

ID    M1 units  M2 units  M3 units
1234  30811 pc  46154 pc  38486 pc
4566  38486 pc  23078 pc  15411 pc
3311  23077 pc  34615 pc  7716 pc

What I'm trying to achieve is a table with following structure:

ID    M    Units    Amounts
1234  1    30811 pc  $400 543,00 
1234  2    46154 pc  $600 000,00 
1234  3    38486 pc  $500 321,00 
4566  1    38486 pc  $500 321,00 
4566  2    23078 pc  $300 012,00 
4566  3    15411 pc  $200 345,00 
3311  1    23077 pc  $300 000,00 
3311  2    34615 pc  $450 000,00 
3311  3    7716 pc   $100 312,00

I would really appreciate your help with this issue.

Answer 1

The following sql script would do the job. Using UNION ALL we turn the Mx units and Mx amount data into rows and save them in temporary tables. Then we join them on ID and M columns.

CREATE TEMPORARY TABLE tmp_t1 AS (
    SELECT *
    FROM (
        SELECT ID, `M1 amount` AS Amount, '1' AS M FROM t1
        UNION ALL
        (SELECT ID, `M2 amount` AS Amount, '2' AS M FROM t1)
        UNION ALL
        (SELECT ID, `M3 amount` AS Amount, '3' AS M FROM t1)
    ) AS x
    ORDER BY ID ASC
);

CREATE TEMPORARY TABLE tmp_t2 AS (
    SELECT *
    FROM (
        SELECT ID, `M1 units` AS Units, '1' AS M FROM t2
        UNION ALL
        (SELECT ID, `M2 units` AS Units, '2' AS M FROM t2)
        UNION ALL
        (SELECT ID, `M3 units` AS Units, '3' AS M FROM t2)
    ) AS x
    ORDER BY ID ASC
);

SELECT
    t1.ID,
    t1.M,
    t2.Units,
    t1.Amount
FROM
    tmp_t1 AS t1
INNER JOIN tmp_t2 AS t2 ON t1.ID = t2.ID AND t1.M = t2.M;

The output

+------+---+----------+-------------+
| ID   | M | Units    | Amount      |
+------+---+----------+-------------+
| 1234 | 1 | 30811 pc | $400 543,00 |
| 1234 | 2 | 46154 pc | $600 000,00 |
| 1234 | 3 | 38486 pc | $500 321,00 |
| 3311 | 1 | 23077 pc | $300 000,00 |
| 3311 | 2 | 34615 pc | $450 000,00 |
| 3311 | 3 | 7716 pc  | $100 312,00 |
| 4566 | 1 | 38486 pc | $500 321,00 |
| 4566 | 2 | 23078 pc | $300 012,00 |
| 4566 | 3 | 15411 pc | $200 345,00 |
+------+---+----------+-------------+

The example data

DROP DATABASE IF EXISTS so_example;
CREATE DATABASE so_example;
USE so_example;

CREATE TABLE t1 (
    ID INT UNSIGNED NOT NULL PRIMARY KEY,
    `M1 amount` VARCHAR(255),
    `M2 amount` VARCHAR(255),
    `M3 amount` VARCHAR(255)
);

INSERT INTO t1 (ID, `M1 amount`, `M2 amount`, `M3 amount`) VALUES
('1234', '$400 543,00', '$600 000,00', '$500 321,00'),
('4566', '$500 321,00', '$300 012,00', '$200 345,00'),
('3311', '$300 000,00', '$450 000,00', '$100 312,00');

CREATE TABLE t2 (
    ID INT UNSIGNED NOT NULL PRIMARY KEY,
    `M1 units` VARCHAR(255),
    `M2 units` VARCHAR(255),
    `M3 units` VARCHAR(255)
);

INSERT INTO t2 (ID, `M1 units`, `M2 units`, `M3 units`) VALUES
('1234', '30811 pc', '46154 pc', '38486 pc'),
('4566', '38486 pc', '23078 pc', '15411 pc'),
('3311', '23077 pc', '34615 pc', '7716 pc');