MySQL pivot column

Question

I'm looking to pivot a single column of data into multiple rows based on a userID in MySQL - I would prefer to do this with an sql query rather than an external tool.

My specific situation seems quite simple but I'm failing to figure it out based on the various pivot question/answer/examples I have found.

Here is the table I have at the moment:

╔════════╦═════════╦═════════╗
║ userID ║ object  ║  index  ║
╠════════╬═════════╣═════════╣
║ user1  ║ Apple   ║    1    ║
║ user1  ║ Orange  ║    2    ║
║ user1  ║ Pear    ║    3    ║
║ user2  ║ Kiwi    ║    1    ║
║ user2  ║ Melon   ║    2    ║
║ user2  ║ Mango   ║    3    ║
║ user3  ║ Apple   ║    1    ║
║ user3  ║ Melon   ║    2    ║
║ user3  ║ Pear    ║    3    ║
║ user3  ║ Mango   ║    4    ║
╚════════╩═════════╩═════════╝

And this is the desired output:

╔════════╦═════════╦═════════╦═════════╦═════════╗
║ userID ║    1    ║    2    ║    3    ║    4    ║
╠════════╬═════════╬═════════╬═════════╬═════════╣
║ user1  ║ Apple   ║ Orange  ║ Pear    ║ empty   ║
║ user2  ║ Kiwi    ║ Melon   ║ Mango   ║ empty   ║
║ user3  ║ Apple   ║ Melon   ║ Pear    ║ Mango   ║
╚════════╩═════════╩═════════╩═════════╩═════════╝

A few things to note:

• Columns, after the userID, should be an index of objects for a given user
• Objects will appear in the same order (I guess that's what a single column/row pivot does anyway)
• The number of objects per userID is variable - column count should match the maximum number of objects of any single user. In the above example, user3 has 4 objects so we have 4 columns after the userID column, even though the other users have less objects. I'm happy to leave blank entries when a user has no data for that column.

I'm sure this is actually quite simple and I'm over thinking it.

Edit: I have slightly edited the tables based on Gordon Linoff's comment to include an ordering index that I have as part of the initial table.

Also, an alternative version of the same question based on Gordon's answer.

If I know up front the maximum number of columns I should have (in this example 4 for objects) does that simplify things? e.g. I could create the table with the 5 columns before I run my query.

Answer 1

You cannot readily do what you want for two important reasons:

• SQL tables represent unordered sets, so there is no ordering unless a column specifies the ordering.
• The columns in a SQL statement are fixed.

What might be close enough is to put all the values in one column:

select userId, group_concat(object)
from table t
group by userId;

You can modify the group_concat() with separator and order by to specify the separator character (default comma) or for a particular ordering.

If you really want a variable number of columns, then you will have to use dynamic SQL -- prepare and exec.