NeoS NeoS - 3 months ago 6
MySQL Question

MySQL pivot table in 1 to n relationship

I have data like this
table master_item


+-------+---------+
|item_id|item_name|
+-------+---------+
| 001 | Car A |
| 002 | Car B |
+-------+---------+


and relation into table process


+-------+--------+
|proc_id|proc_seq|
+-------+--------+
| 001 | 1 |
| 001 | 2 |
| 001 | 3 |
| 001 | 4 |
| 001 | 5 |
| 002 | 1 |
| 002 | 2 |
| 002 | 3 |
+-------+--------+


and i want result like this when i select item_id = 001


+-------+---------+-----+-----+-----+-----+-----+-----+-----+
|item_id|item_name|proc1|proc2|proc3|proc4|proc5|proc6|proc7|
+-------+---------+-----+-----+-----+-----+-----+-----+-----+
| 001 | Car A | 1 | 2 | 3 | 4 | 5 | | |
+-------+---------+-----+-----+-----+-----+-----+-----+-----+


what is the query to produce this result ?

Answer

The problem lies in PIVOT TABLE category.

Here you can accomplish your result through the query given below:

SET @sql := '';
SELECT 
CONCAT('SELECT 
MI.item_id,
MI.item_name,',
GROUP_CONCAT('MAX(CASE WHEN P.proc_seq =', P.proc_seq ,' THEN P.proc_seq END) AS ', 'proc',P.proc_seq,' '),
'FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id
WHERE MI.item_id = 1
GROUP BY P.proc_id') INTO @sql
FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id
WHERE item_id = 1
GROUP BY P.proc_id;


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

WORKING DEMO

But like I said, better do this kind of job in application logic. Otherwise it will be too cumbersome to make it done through MySQL alone.

EDIT:

In order to get result for each item_id

SET @sql := '';
SELECT 
CONCAT('SELECT 
MI.item_id,
MI.item_name,',
GROUP_CONCAT('MAX(CASE WHEN P.proc_seq =', P.proc_seq ,' THEN P.proc_seq END) AS ', 'proc',P.proc_seq,' '),
'FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id
GROUP BY P.proc_id') INTO @sql
FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id

WHERE
    item_id = (
        SELECT
            maxProcId.proc_id
        FROM
            (
                SELECT
                    proc_id,
                    COUNT(proc_seq) total
                FROM process
                GROUP BY proc_id
                ORDER BY total DESC
                LIMIT 1
            ) AS maxProcId
    )
GROUP BY
    P.proc_id;


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

Demonstrated here

Comments