Pivot Table MySQL

Question

If MySQL table looking something like this

And what I'm trying to do is a pivot table looks like this:

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

each column based on their occupation

Related: [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/q/12004603/2298301) — Dhruv Saxena, May 03 '17 at 20:42
The data looks suspiciously similar to the data in the coding challenge described in this question a couple of weeks ago: https://stackoverflow.com/questions/46210995/ so I wonder if you're also trying to do a coding challenge. The point of a coding challenge is for YOU to solve it, not to get people on Stack Overflow to do it for you! — Bill Karwin, Sep 25 '17 at 14:51

score 0 · Answer 1 · edited Sep 25 '17 at 14:42

0

This code will do something near what you want:

set @sql = (
    select group_concat(distinct 
        concat(
            "(case when `Occupation`='", Occupation, "' then `Name` end) as `", `Occupation`, "`"
        )
    ) 
    from t
);

set @sql = concat("select ", @sql, " from t ");

prepare stmt from @sql;

execute stmt;

edited Sep 25 '17 at 14:42

Graham

7,431
18
59
84

answered May 03 '17 at 20:42

Filipe Martins

608
10
23

I reach such answer but I cannot rid of Null values – Ahmad Khasawneh May 03 '17 at 20:58
`IFNULL(expression, 0)` – Rick James Sep 26 '17 at 00:40

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

There are plenty of ways of achieving this described here: MySQL pivot table

where the dynamic way of doing it is included in the answer by Abhishek Gupta using GROUP_CONCAT with CONCAT. It is not fast but it is dynamic.

The gist of it is that if you want a dynamic solution then you first have to do a query to find out what columns there are in your data. Then you create a new query based in that information.

If you want a dynamic solution that is also fast then you need to pull the first query into your programming language (like php or whatever you are using), and use the programming language to create a query that is fast.

Pivot Table MySQL

2 Answers2