mysql pivot table with string values

Question

I have a table with just two columns like this

mysql> select * from stuff_table;
+------+-------+
| name | stuff |
+------+-------+
| John | shoes |
| Jim  | bag   |
| Ely  | book  |
| Tom  | bag   |
| Will | shoes |
| Pitt | book  |
| Dean | bag   |
| Luke | bag   |
+------+-------+

and I tried with many solutions I found like

select distinct
max(case when stuff='shoes' then name else name is null) end as shoes,
max(case when stuff='bag' then name else name is null end) as bag,
max(case when stuff='book' then name else name is null end) as book
from stuff_table;

but I just got this

+-------+------+------+
| shoes | bag  | book |
+-------+------+------+
| Will  | Tom  | Pitt |
+-------+------+------+

Instead, I want to get this

+-------+------+------+
| shoes | bag  | book |
+-------+------+------+
| John  | Jim  | Ely  |
| Will  | Tom  | Pitt |
| NULL  | Dean | NULL |
| NULL  | Luke | NULL |
+-------+------+------+

I have tried with sum(case...) or if(case..) or group by too but it doesn't work. Is there any mysql query to get such that table? Kindly help. Thank you.

Your sample table is missing a column which would tell to which row in the pivoted output each data point would map. Translation: there isn't enough information to answer this. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 22, 2018 at 14:34
This is a kind of problem that is much easier to solve in an application language, not SQL. — Rick James
– Rick James, Commented Jul 12, 2018 at 2:37

sgeddes · Accepted Answer · 2018-06-22 15:01:21Z

7

Depending on the version of mysql you are using, here's one approach establishing a row_number per group, then using conditional aggregation grouped by that row number:

select 
    rn, 
    max(case when stuff = 'bag' then name end) 'bag',
    max(case when stuff = 'book' then name end) 'book',
    max(case when stuff = 'shoes' then name end) 'shoes' 
from (
  select *, row_number() over (partition by stuff order by name) rn
  from stuff_table
) t
group by rn

Fiddle Demo

Since you are using an older version of mysql, you'll need to use user-defined variables to establish the row number. The rest then works the same. Here's an example:

select 
    rn, 
    max(case when stuff = 'bag' then name end) 'bag',
    max(case when stuff = 'book' then name end) 'book',
    max(case when stuff = 'shoes' then name end) 'shoes' 
from (
  select *, 
  ( case stuff 
         when @curStuff
         then @curRow := @curRow + 1 
         else @curRow := 1 and @curStuff := stuff 
   end
  ) + 1 AS rn
  from stuff_table, (select @curRow := 0, @curStuff := '') r
  order by stuff
) t
group by rn

More Fiddle

edited Jun 22, 2018 at 15:01

answered Jun 22, 2018 at 14:47

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

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

3 Comments

Yarp Over a year ago

what mysql version did you use? I used mysql 5.7

sgeddes Over a year ago

@Yarp As I mentioned, older versions of mysql don't support row_number. You need to use user-defined variables instead. Here's a working fiddle: db-fiddle.com/f/skbKMWPCGJFLURLmkcouD4/1

programmer-man Over a year ago

What specific version what used for the first solution? It looks more elegant.

HamzaNig · Accepted Answer · 2018-06-22 14:47:09Z

-1

You dont need to use else and You have a end outside of max function :

select distinct
max(case when stuff='shoes' then name end ) as shoes,
max(case when stuff='bag' then name  end) as bag,
max(case when stuff='book' then name  end) as book
from stuff_table;

answered Jun 22, 2018 at 14:47

HamzaNig

1,0271 gold badge10 silver badges33 bronze badges

2 Comments

Yarp Over a year ago

My bad, the one that I posted return NULL but with eight rows. I just mixed up with so many solutions but didn't find a suitable one

Yarp Over a year ago

it works but return the same number of rows as the first table where I want only four rows at max

Collectives™ on Stack Overflow

mysql pivot table with string values

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related