I'm having trouble creating a pivot table in MySQL using the following tables:
Teams
-------------
id | name
1 | teamA
Processes
-------------
id | name
1 | processA
2 | processB
ProcessDetails
---------------
id | processId | keyName
1 | 1 | shape
2 | 1 | vegetable
3 | 1 | fruit
4 | 2 | animal
TeamProcesses
-----------------
id | teamId | processId
5 | 1 | 1
6 | 1 | 2
TeamProcessDetails
--------------------
id | teamProcessId | proccessDetailsId | value
1 | 5 | 1 | circle
2 | 5 | 2 | carrot
3 | 5 | 3 | apple
4 | 6 | 4 | dog
The pivot table I'm trying to produce is this one:
Pivot Table
------------
teamId | processId | shape | vegetable | fruit | animal
1 | 1 | circle | carrot | apple | NULL
1 | 2 | NULL | NULL | NULL | dog
It should be noted that the number of keys is dynamic so I think I need to use the prepared statement method. Also, the processes don't have the same keys so they should only have a value for a key that belongs to that process.
Thanks!
When you are trying to pivot dynamic or unknown value, I would always suggest that you start with a static or hard-coded version of the query first, then convert it to dynamic SQL.
MySQL doesn't have a PIVOT function so you will need to use an aggregate function with a CASE expression to get the result. The static version of the code will be similar to the following:
select t.id teamid,
t.name teamname,
p.id processid,
p.name processname,
max(case when pd.keyname = 'shape' then tpd.value end) shape,
max(case when pd.keyname = 'vegetable' then tpd.value end) vegetable,
max(case when pd.keyname = 'fruit' then tpd.value end) fruit,
max(case when pd.keyname = 'animal' then tpd.value end) animal
from teams t
inner join teamprocesses tp
on t.id = tp.teamid
inner join TeamProcessDetails tpd
on tp.id = tpd.teamProcessId
inner join processes p
on tp.processid = p.id
inner join processdetails pd
on p.id = pd.processid
and tpd.processDetailsid = pd.id
group by t.id, t.name, p.id, p.name;
See SQL Fiddle with Demo.
Now if you are going to have an unknown number of keynames that you want to convert into columns, then you will need to use a prepared statement to generate dynamic SQL. The code will be similar to:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when pd.keyname = ''',
keyname,
''' then tpd.value end) AS ',
replace(keyname, ' ', '')
)
) INTO @sql
from ProcessDetails;
SET @sql
= CONCAT('SELECT t.id teamid,
t.name teamname,
p.id processid,
p.name processname, ', @sql, '
from teams t
inner join teamprocesses tp
on t.id = tp.teamid
inner join TeamProcessDetails tpd
on tp.id = tpd.teamProcessId
inner join processes p
on tp.processid = p.id
inner join processdetails pd
on p.id = pd.processid
and tpd.processDetailsid = pd.id
group by t.id, t.name, p.id, p.name;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo.
One thing to keep in mind the GROUP_CONCAT function to create the string of columns has a default max length of 1024, so if you are going to have a lot of characters in this string you might have to alter the session value for the group_concat_max_len.
This query will give a result:
| TEAMID | TEAMNAME | PROCESSID | PROCESSNAME | SHAPE | VEGETABLE | FRUIT | ANIMAL |
| 1 | teamA | 1 | processA | circle | carrot | apple | (null) |
| 1 | teamA | 2 | processB | (null) | (null) | (null) | dog |
