CODEWITHSUNDEEP
mysql
Omar Makled
Omar Makled

Reputation: 147

how to write query to get that result

I have table named myTable contains some data like below

id type name
1   1    A
2   2    B
3   3    C
4   2    D
5   3    E
6   3    F

how to write query to get results like below id:name 

type1  type2 type3
1:A     2:B   3:C
null    4:D   5:E
null    null  6:F

Upvotes: 1

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Often, this type of operation is more easily done in the application layer. However, you can do this by enumerating each row for each type and then aggregating by it.

I think this does what you want:

select max(case when type = 1 then concat_ws(':', id, type) end) as type1,
       max(case when type = 2 then concat_ws(':', id, type) end) as type2,
       max(case when type = 3 then concat_ws(':', id, type) end) as type3
from (select t.*,
             (@rn := if(@t = type, @rn + 1,
                        if(@t := type, 1, 1)
                       )
             ) as rn
      from table t cross join
           (select @rn := 0, @t := -1) vars
      order by type, id
     ) t
group by rn

Upvotes: 1

Related Questions