带有动态列的数据透视表 [英] Pivot table with dynamic columns

问题描述

我无法使用以下表在MySQL中创建数据透视表:

I'm having trouble creating a pivot table in MySQL using the following tables:

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

我要生成的数据透视表应该只包含给定团队的流程详细信息.

The pivot table I am trying to produce should only contain the process details for a given team.

示例

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

谢谢！

推荐答案

使用您的中的查询上一个问题，您应该可以更改代码以添加WHERE子句，该子句将过滤每个团队的数据:

Using the query from your previous question you should be able to alter the code to add a WHERE clause that will filter the data for each team:

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小提琴

这篇关于带有动态列的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！