您好,我正试图在mysql中成功地选择行作为列。
也许有人能帮我。
这是我桌子的样子

+----------+----------------------------------------------------+----------+
| brand_id | brand_name                                         | linecode |
+----------+----------------------------------------------------+----------+
| DPQT     | 1-800 Tow Truck                                    | DER      |
| DPQT     | 1-800 Tow Truck                                    | DCF      |
| DPQT     | 1-800 Tow Truck                                    | DCA      |
| DPQT     | 1-800 Tow Truck                                    | AAA      |
| DPQT     | 1-800 Tow Truck                                    | DDD      |
| BLGR     | 1-800-Radiator                                     | Curt     |
| BGVM     | 100+ Manufacturing/Coyote                          | ASC      |
| DPQS     | 10C Technologies Inc                               | AQW      |
| DPQS     | 10C Technologies Inc                               | ASQ      |
| FDJG     | 2 Cool AirVents                                    | CAS      |

我试着让结果看起来像:
   +----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| brand_id | brand_name                                         | Code1 | Code2 | Code3 | Code4 | Code5 | Code6 | Code7 | Code8 | Code9 | Code10 |
+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| DPQT     | 1-800 Tow Truck                                    | DCF   | DCA   | AAA   | DDD   | DER   | NULL  | NULL  | NULL  | NULL  | NULL   |
| BLGR     | 1-800-Radiator                                     | Curt  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |
| BGVM     | 100+ Manufacturing/Coyote                          | ASC   | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |
| DPQS     | 10C Technologies Inc                               | ASQ   | AQW   | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |
| FDJG     | 2 Cool AirVents                                    | CAS   | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |

如您所见,当brand_id和brand_name相同时,我希望将行代码分组为单独的列
最大行代码是10。这就是为什么我有代码1-10
这就是我现在正在做的,它不起作用。
$sql0 = 'set @num := 0';
$result = mysqli_query($dbh,"select brand_id,   brand_name,
max(case when group_row_number = 1 then linecode end) Code1,
max(case when group_row_number = 2 then linecode end) Code2,
max(case when group_row_number = 3 then linecode end) Code3,
max(case when group_row_number = 4 then linecode end) Code4,
max(case when group_row_number = 5 then linecode end) Code5,
max(case when group_row_number = 6 then linecode end) Code6,
max(case when group_row_number = 7 then linecode end) Code7,
max(case when group_row_number = 8 then linecode end) Code8,
max(case when group_row_number = 9 then linecode end) Code9,
max(case when group_row_number = 10 then linecode end) Code10
from (  select brand_id, brand_name, linecode, @num := @num + 1 as group_row_number from linecodes_temp ) src
group by brand_id, brand_name
order by if(linecode = '' or linecode is null,1,0), brand_name ASC");

我得到的结果是
+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| brand_id | brand_name                                         | Code1 | Code2 | Code3 | Code4 | Code5 | Code6 | Code7 | Code8 | Code9 | Code10 |
+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| DPQT     | 1-800 Tow Truck                                    | DER   | DCF   | DCA   | AAA   | DDD   | NULL  | NULL  | NULL  | NULL  | NULL   |
| BLGR     | 1-800-Radiator                                     | NULL  | NULL  | NULL  | NULL  | NULL  | Curt  | NULL  | NULL  | NULL  | NULL   |
| BGVM     | 100+ Manufacturing/Coyote                          | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | ASC   | NULL  | NULL  | NULL   |
| DPQS     | 10C Technologies Inc                               | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | AQW   | ASQ   | NULL   |
| FDJG     | 2 Cool AirVents                                    | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | CAS    |

我需要一种方法,以便一旦brand_id和brand_name更改,num变量返回到0。
正如您所看到的,它正在做我希望它做的事情,但是一旦它转到下一行,num变量就会继续向上转到下一列。所以这就是下一行代码被添加的地方。我想我需要一个if语句,但是我不知道怎么做。
如果有人能更新我的查询以便发生这种情况,我将非常感谢
谢谢你抽出时间。

最佳答案

您所说的是部分转置,PHP并不是自动转置的。不过,你可以自己做一些小的修改。
在查询中,使用GROUP_CONCAT()

SELECT brand_id, brand_name, GROUP_CONCAT(linecode) AS codes brands GROUP BY brand_id, brand_name

在PHP中,使用explode()获取不同的代码:
$codes = explode(',', $row['codes']);

注意:我对您的基本表结构做了一些假设。相应地调整查询。

07-26 04:52