Selecting rows as columns in MYSQL

Question

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

| 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

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

| 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

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

Thank you all for your help

Sure, it's possible. But it's going to be horribly ugly. Are you sure that you need to do this operation in the database layer? Perhaps it's more appropriate in your presentation layer? How exactly are you intending to use this resultset? — eggyal
– eggyal, Commented Nov 29, 2012 at 14:30

Taryn · Accepted Answer · 2012-11-29 14:40:04Z

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.:

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

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) |

Collectives™ on Stack Overflow

Selecting rows as columns in MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related