在MYSQL中选择行作为列

Hello i am trying to figure out how to do this i have a mysql table

你好，我想知道怎么做这个我有一个mysql表。

| ID |    ACC_ID  | line_id | code     |
| 1  |          1 |    5960 | DCA      |
| 2  |          1 |    5960 | AAA      |
| 3  |          1 |    5960 | DDD      | 
| 4  |          1 |    5960 | DER      |
| 5  |          1 |    5054 | DCB      |
| 6  |          1 |    5054 | AAC      |
| 7  |          1 |    5011 | DDE      |
| 8  |          1 |    5012 | DEQ      |

etc the database goes down about 10000 lines

数据库大约减少了10000行。

I would like to make a mysql select statement that will do this

我想做一个mysql select语句来实现这一点

| ACC_ID     | line_id | code     | code     | code     | code     | 
|   1        | 5960    | DCA      | AAA      | DDD      | DER      | 
|   1        | 5054    | DCB      | DER      |          |          |
|   1        | 5011    | DDE      |          |          |          |
|   1        | 5012    | DEQ      |          |          |          |

there could be up to ten codes per line_id

每个line_id最多有10个代码

Now my question is it possible to make the query above using a select statement.

现在我的问题是，是否可以使用select语句进行上述查询。

Thank you all for your help

谢谢大家的帮助

1 个解决方案

#1

This is a PIVOT but MySQL does not have a PIVOT function but you can replicate it with an aggregate function and a CASE statement. MySQL also does not have the easiest ways to assign row number by group, but the following is a sample of how you could achieve this using SQL. Since you said you can have up to 10 codes per line_id I hard-coded a possible solution.:

这是一个PIVOT，但是MySQL没有一个PIVOT函数，但是可以使用聚合函数和CASE语句来复制它。MySQL也没有按组分配行号的最简单方法，但是下面是使用SQL实现这一点的示例。因为你说过每个line_id最多可以有10个代码，所以我硬编码了一个可能的解决方案。

select acc_id,
  line_id,
  max(case when group_row_number = 1 then code end) Code1,
  max(case when group_row_number = 2 then code end) Code2,
  max(case when group_row_number = 3 then code end) Code3,
  max(case when group_row_number = 4 then code end) Code4,
  max(case when group_row_number = 5 then code end) Code5,
  max(case when group_row_number = 6 then code end) Code6,
  max(case when group_row_number = 7 then code end) Code7,
  max(case when group_row_number = 8 then code end) Code8,
  max(case when group_row_number = 9 then code end) Code9,
  max(case when group_row_number = 10 then code end) Code10
from
(
  select ACC_ID,
    line_id,
    code, 
    @num := if(@ACC_ID = `ACC_ID` AND @line_id = `line_id`, @num + 1, 1) as group_row_number,
    @ACC_ID := `ACC_ID` as dummy,
    @line_id := `line_id` as linedummy
  from yourtable
) src
group by acc_id, line_id
order by line_id desc

See SQL Fiddle with Demo

参见SQL小提琴演示

Result:

结果:

| ACC_ID | LINE_ID | CODE1 |  CODE2 |  CODE3 |  CODE4 |  CODE5 |  CODE6 |  CODE7 |  CODE8 |  CODE9 | CODE10 |
-------------------------------------------------------------------------------------------------------------
|      1 |    5960 |   DCA |    AAA |    DDD |    DER | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |    5054 |   DCB |    AAC | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |    5012 |   DEQ | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |    5011 |   DDE | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |