0

Let's say that I have this table:

+-----------+-------+
| name      | seat  |
+-----------+-------+
| Andrew    | 1     |
| Andrew    | 5     |
| Andrew    | 15    |
| Billy     | 2     |
| Billy     | 5     |
+-----------+-------+

Is there any way for every name, to SELECT the corresponding occurrences of seat and append them in a single row?

Desired output:

+-----------+-------+-------+-------+
| name      | seat1 | seat2 | seat3 |
+-----------+-------+-------+-------+
| Andrew    | 1     | 5     | 15    |
| Billy     | 2     | 5     | NULL  |
+-----------+-------+-------+-------+




I tried a simple SELECT with GROUP:

SELECT `name`, seat
FROM users
GROUP BY `name`
ORDER BY `name`;


But it of course outputs 1 column seat for every name.

+-----------+-------+
| name      | seat  |
+-----------+-------+
| Andrew    | 1     |
| Billy     | 2     |
+-----------+-------+



Is that even possible and how?

mortaZa
  • 115
  • 1
  • 1
  • 8
  • 1
    Yes, it's possible, it's called pivoting your results. MySql doesn't support the pivot statement, so you'll need to use a prepared statement. Here's an example: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – sgeddes Sep 11 '14 at 21:55
  • Generally, it's best to handle display logic in the presentation layer - assuming you have that – Strawberry Sep 11 '14 at 22:18
  • @sgeddes Thanks for this info, I didn't know it was called that way. I always say that using the right keywords can get you the right information. The problem is knowing what they are :D – mortaZa Sep 12 '14 at 00:05

1 Answers1

1

since you are using group by

you need aggregate funcion

group_concat is the one you might have to use,

  select name, group_concat(seat, SEPARATOR ",") from ...

it should produce something like 

  +-----------+-------+
  | name      | seats |
  +-----------+-------+
  | Andrew    | 1,5,15|
  | Billy     | 2,5   |
  +-----------+-------+

sure it is a single column with all the strings in it separated by SEPARATOR string not multiple columns, but hopefully that will be useful/usable

otherwise you may have to then do some more string manipulation to use include the group_concat(seat, SEPARATOR ",") multiple times and parse the positions from it somehow, e.g. 

    select SUBSTRING_INDEX(group_concat(seat, SEPARATOR ","), ",",1), SUBSTRING_INDEX(group_concat(seat, SEPARATOR ","), ",",2), SUBSTRING_INDEX(group_concat(seat, SEPARATOR ","), ",",3) from ....

of course that is also not precisely what you set out to get - I don't see how the number of columns selected can be made dynamic

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

hope this helps

Tjunkie
  • 498
  • 4
  • 7
  • It can be made dynamic. It's covered elsewhere - but it's a bit tedious – Strawberry Sep 11 '14 at 22:19
  • Yes it helps a lot. I didn't even think that the fields could be concatenated in one. Inspired by you, I tried a bit more complex stuff like `GROUP_CONCAT( CONCAT( column1, ' some string ', column2 ) SEPARATOR '
    ' )` and it suited me great.     – mortaZa Sep 12 '14 at 00:23