I need to transpose data in mysql with duplicate values in column one and unique values in column 2

Question

With the following table of information:

id | job | code
1  | 100 | ab123
2  | 100 | ab124
3  | 101 | ab125
4  | 101 | ab126
5  | 101 | ab127
6  | 102 | ab128
7  | 102 | ab129
8  | 103 | ab130
9  | 104 | ab131

I need to get the following table to use the 3 codes in other queries:

job | code1 | code2 | Code3
100 | ab123 | ab124 |
101 | ab125 | ab126 | ab127
102 | ab128 | ab129 |
103 | ab130 |       |
104 | ab131 |       |

There will only ever be 3 codes.

Thanks in advance

Have you tried anything? – Bhavik Shah May 25 '16 at 11:24 — Bhavik Shah, May 25 '16 at 11:24

score 3 · Accepted Answer · edited May 23 '17 at 12:31

This is a bit tricky with MySQL (compared to other databases with window- and pivot functions) but you can do this by using a query that numbers rows within each group of Job items as a derived table and use that as a source for an outer query with conditional aggregation:

select 
  job,
  max(case when rn = 1 then code end) as Code1,
  max(case when rn = 2 then code end) as Code2,
  max(case when rn = 3 then code end) as Code3
from (
  select id,     
       code,
       case job 
           when @grp then @row := @row + 1 
           else @row := 1
       end as rn,
       @grp := job as job
  from Table1 t
  join (select @row := 0, @grp := '') r
  order by id, job
) s
group by job

I assumed that the Code items should be ordered by ID, but if you want if by Code, just change the order by clause in the derived table.

The numbering part is based on answers to this question.

Sample SQL Fiddle

Sample output:

| job | Code1 |  Code2 |  Code3 |
|-----|-------|--------|--------|
| 100 | ab123 |  ab124 | (null) |
| 101 | ab125 |  ab126 |  ab127 |
| 102 | ab128 |  ab129 | (null) |
| 103 | ab130 | (null) | (null) |
| 104 | ab131 | (null) | (null) |

I need to transpose data in mysql with duplicate values in column one and unique values in column 2

1 Answers1