Pivot table with dynamic columns

Question

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.

Example

For Team A:

Pivot Table
------------
teamId | processId |  shape  | vegetable |  fruit  | animal
   1   |     1     |  circle |  carrot   |  apple  |  NULL
   1   |     2     |  NULL   |   NULL    |   NULL  |  dog

For Team B:

teamId | processId | dessert
   2   |     3     | chocolate

Thanks!

How dynamic should it be? Do you know at design time how many columns there will be and their titles? Also, please post your own best attempt to solve the problem. — PM 77-1
– PM 77-1, Commented Sep 27, 2013 at 19:31
Sorry I should have mentioned this was a followup question from another I posted a while ago. @bluefeet had helped me out before. stackoverflow.com/questions/18729532/… — hebime
– hebime, Commented Sep 27, 2013 at 20:18

Community · Accepted Answer · 2017-05-23 12:28:03Z

2

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;

See SQL Fiddle with Demo

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Sep 27, 2013 at 19:47

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hebime Over a year ago

Thanks for the help again. I couldn't get it to work because of the subquery.

Collectives™ on Stack Overflow

Pivot table with dynamic columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related