MySQL Pivot Table and Numbered Columns

Question

I'm working on a query that will take a table, and join it as a pivot table using dynamic SQL. I've read up on a few questions here on Stack Overflow, as well as the rest of the internet, and have not been able to find a solution to what I'm trying to accomplish.

I have a table like so

+---------------------------------------+
| ID   First_name    Last_name    owner |
+---------------------------------------+
| 1    Joe           Bob          1     |
| 2    Fluffy        Bob          0     |
| 3    Killer        Bob          0     |
| 4    Bob           Joe          1     |
| 5    Yapper        Bob          0     |
+---------------------------------------+

And a join table to itself like so (don't ask me why it joins to itself, I didn't design it)

+-----------------------------------+
| contact_id    relation_contact_id |
+-----------------------------------+
| 1             2                   |
| 1             3                   |
| 4             5                   |
+-----------------------------------+

As such, there is a chance that each user owns one or more pets. The pivot I need is of the pet's names for each owner, and I'd like it to be numbered (ie Pet1_name, Pet2_name, etc) Like so

+---------------------------------------------------+
| First_name    Last_name    Pet1_name    Pet2_name |
+---------------------------------------------------+
| Joe           Bob          Fluffy       Killer    |
| Bob           Joe          Yapper       NULL      |
+---------------------------------------------------+

The main issue I'm having here is getting the numbers to work correctly as I go. Currently, it's not resetting back to 0 after each row, so I'll get a bunch of null entries. Using the data from above, this is how my result set currently looks:

+----------------------------------------------------------------+
| First_name    Last_name    Pet1_name    Pet2_name    Pet3_name |
+----------------------------------------------------------------+
| Joe           Bob          Fluffy       Killer       NULL      |
| Bob           Joe          NULL         NULL         Yapper    |
+----------------------------------------------------------------+

Here is the SQL I'm running

SET @sql = NULL;
SET @counter = 0;
Select GROUP_CONCAT(distinct
CONCAT('MAX(case when c2.id = ''', cc.relation_contact_id, 
       ''' AND c.id = ''', cc.contact_id, ''' 
       then c2.first_name end) AS ', CONCAT('`Pet_', @counter := @counter + 1, '_Name`')
)) INTO @sql
From contacts_contacts as cc;
SET @counter = 0;

SET @sql = CONCAT('SELECT c.id, c.first_name, c.last_name, c.email, ', @sql, '
FROM contacts c 
LEFT JOIN contacts_contacts cc on c.id = cc.contact_id 
LEFT JOIN contacts c2 on c2.id = cc.relation_contact_id
WHERE c.owner = 1
GROUP BY c.id
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And here is a SQLFiddle with a working example.

Thanks in advance for the help, looking forward to getting this working!

score 0 · Accepted Answer · edited May 23 '17 at 11:52

I got it to work with this query, but it's a bit of a performance hog so I might be switching over to PHP for this. Found my answer on this question, so someone can mark this as a Dupe if they'd like to.

In the meantime, here's a link to the updated SQLFiddle with the query working.

SET @sql = NULL;
Select 
  GROUP_CONCAT(distinct
    CONCAT('max(case when rownum = ', 
           rownum, 
           ' then c2.first_name end) AS Pet_', rownum, '_Name'
    )
  ) INTO @sql
from
(  
  select contact_id, relation_contact_id,
    @row:=if(@prev=contact_id, @row,0) + 1 as rownum,
    @prev:=contact_id 
  from contacts_contacts, (SELECT @row:=0) r
) cc;

SET @sql = CONCAT('SELECT c.id, c.first_name, c.last_name, c.email as e, ', @sql, '
FROM
(
  select contact_id, relation_contact_id,
    @row:=if(@prev=contact_id, @row,0) + 1 as rownum,
    @prev:=contact_id 
  from contacts_contacts, (SELECT @row:=0) r
) cc
LEFT JOIN contacts c on c.id = cc.contact_id 
LEFT JOIN contacts c2 on c2.id = cc.relation_contact_id
WHERE c.owner = 1
GROUP BY e
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

score -1 · Answer 2 · answered Apr 20 '15 at 23:50

-1

The Stored Procedure in here will generate the SQL for you.

answered Apr 20 '15 at 23:50

Rick James

135,179
13
127
222

Thanks Rick James. I got it working on my own, so I didn't get a chance to try out your stored procedure. Looking at your description though, I don't think it would give me what I'm asking for. I need to variably build the columns as I don't know how many pets each user has, and I don't have a row that I can use to define the column names. If I'm just not understanding what you've got, feel free to let me know. – hugmungus Apr 21 '15 at 19:09

MySQL Pivot Table and Numbered Columns

2 Answers2