How to convert rows to columns in mysql

Mysql method to implement row conversion: 1. Use cross join to implement, the code is [cross join(select sum(a.kills) 'Zhu Bajie]]; 2. Use case statement to implement, the code It is [case when user_name='Sun Wukong'].

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, this method is suitable for all brands of computers.

Mysql method to implement row conversion:

1. Use cross join to achieve

select * from 
(select sum(a.kills) '孙悟空' 
  from kills1 as a 
  LEFT JOIN tssrz as b 
    on a.user_id = b.id 
 WHERE b.user_name = '孙悟空' 
 GROUP BY b.user_name) e
 cross join 
 (select sum(a.kills) '猪八戒' 
  from kills1 as a 
  LEFT JOIN tssrz as b 
    on a.user_id = b.id 
 WHERE b.user_name = '猪八戒' 
 GROUP BY b.user_name) f

2. Use case statements to implement

SELECT sum(case when user_name='孙悟空' then kills end) as '孙悟空',
  sum(case when user_name='猪八戒' then kills END) as '猪八戒',
  sum(case when user_name='沙和尚' then kills END) as '沙和尚'
   from tssrz as a
   join kills1 as b
     on a.id = b.user_id

Related free learning recommendations: mysql video tutorial

The above is the detailed content of How to convert rows to columns in mysql. For more information, please follow other related articles on the PHP Chinese website!