使用以下表在MySQL中创建透视表时遇到问题：
Teams
-------------
id | name
1 | teamA
2 | teamB
Processes
-------------
id | name
1 | processA
2 | processB
3 | processC
ProcessDetails
---------------
id | processId | keyName
1 | 1 | shape
2 | 1 | vegetable
3 | 1 | fruit
4 | 2 | animal
5 | 3 | dessert
TeamProcesses
-----------------
id | teamId | processId
5 | 1 | 1
6 | 1 | 2
7 | 2 | 3
TeamProcessDetails
--------------------
id | teamProcessId | proccessDetailsId | value
1 | 5 | 1 | circle
2 | 5 | 2 | carrot
3 | 5 | 3 | apple
4 | 6 | 4 | dog
5 | 7 | 5 | chocolate
我试图生成的透视表应该只包含给定团队的流程详细信息。
例子
对于A组：
Pivot Table
------------
teamId | processId | shape | vegetable | fruit | animal
1 | 1 | circle | carrot | apple | NULL
1 | 2 | NULL | NULL | NULL | dog
对于B组：
teamId | processId | dessert
2 | 3 | chocolate
谢谢！
最佳答案
使用previous question中的查询，您应该能够更改代码以添加一个WHERE子句，该子句将筛选每个团队的数据：
SET @sql = NULL;
set @team = 'teamA';
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when pd.keyname = ''',
keyname,
''' then tpd.value end) AS ',
replace(keyname, ' ', '')
)
) INTO @sql
from ProcessDetails
where processId in (select tp.processId
from teams t
inner join teamprocesses tp
on t.id = tp.teamid
where t.name = @team);
SET @sql
= CONCAT('SELECT t.id teamid,
t.name teamname,
p.id processid, ', @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
where t.name = ''', @team, '''
group by t.id, t.name, p.id, p.name;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
见SQL Fiddle with Demo
关于mysql - 带有动态列的数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19058595/