根据某些条件将行转换为列 [英] Convert rows to columns with some conditions

问题描述

这是桌子

id 值名称

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

有可能吗?

推荐答案

您可以尝试使用 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`

示例

或 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

这篇关于根据某些条件将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！