Convert rows to columns with some conditions

问题:

this is the table

id value name

1    2    first
2    2    manger 
3    2    islam
4    2    cairo
5    3    frist 
6    3    manger 
7    3    ahmed
8    3    alex
9    4    second 
10   4    manger 
11   4    mark
12   4    london

want to make this columns as row and select with some condtions that all 'first'

     c1       c2       c3      c4 
R1   first   manger    islam   cairo

R2   first   manger    ahmed   alex

It's possible ?

回答1:

You can try using CASE :

SELECT GROUP_CONCAT((CASE WHEN `id`%4 = 1 THEN `name` END)) AS `c1`
     , GROUP_CONCAT((CASE WHEN `id`%4 = 2 THEN `name` END)) AS `c2`
     , GROUP_CONCAT((CASE WHEN `id`%4 = 3 THEN `name` END)) AS `c3`
     , GROUP_CONCAT((CASE WHEN `id`%4 = 0 THEN `name` END)) AS `c4`
FROM `tbl`
GROUP BY `value`

Example

or IF

SELECT GROUP_CONCAT(IF(`id`%4 = 1 ,`name`, null)) AS `c1`
     , GROUP_CONCAT(IF(`id`%4 = 2 ,`name`, null)) AS `c2`
     , GROUP_CONCAT(IF(`id`%4 = 3 ,`name`, null)) AS `c3`
     , GROUP_CONCAT(IF(`id`%4 = 0 ,`name`, null)) AS `c4`
FROM `tbl`
GROUP BY `value`

SQLFiddle